Using VBA to change a time stamp to PM based on a previous time stamp

user0P6m1 0
Viewed 24

I'm creating a time sheet that tracks projects worked on throughout the day. I have two columns, one that shows the time a project is started and one that shows when it ends. The first column is easy because it autofills from the previous project's end time; however, I want to make entering the end time fast and easy. Excel automatically defaults to AM if PM isn't specifically typed in, so I want to code the sheet so that if the time entered in the project end time is less than the project begin time, then it automatically adds 12hrs so that it becomes PM. I don't want to use Military time, because I am trying to make this easy for everyone in the office to use. I also never expect for it to be used past midnight and so that shouldn't be a complication. This is the code that I came up with, but it's giving me an "object required" error.

Sub AM2PM()

If Not Intersect(Target, Range("J6:J50")) Is Nothing Then

If Target = Range("J6:J50") Then
    
    If Target.Value < Target.Offset(0, -1).Value Then
       
        Target.Value = Target.Value + 0.5
    
    End If

End If

End If

End Sub

Please excuse me if my code is completely off base; I've only started learning VBA about two weeks ago and only on an 'as needed' bases.

time compare vba
1 Answers

It's not clear at all what you are doing with 'Range("J6:J50")' but don't you need

  If Target.Address  = "$j$6:$J$50" ?

I get the feeling this will help:

  Dim zCell As Range
  For Each zCell In Target
    If zCell.Value < zCell.Offset(0, -1).Value Then
      zCell.Value = zCell.Value + 0.5
    End If
  Next zCell
dcromley0
Related