Help with VBA

[terrib54]

I have the following code that works but it replaces the entire line. I need multiple ranges. I tried but when I posed something in column it overwrote all the other cell dates. I need each to be stamped individually so I missed something.

I apologize ahead of time. I'm new - trying hard but not having any luck. Can anyone tell me how to have each range only post the date/time in the column just to the left of it. IE: J5:36 posts to I5:I36, L5:L36 posts to K5:36.

This is a 12 month spreadsheet and once a month a post if made via formula that needs to post the time data is posted. Since a formula pulls the data from other sheets I need the Worksheet_Calculate function.

Thanks in advance.

Private Sub Worksheet_Calculate()
Dim rng As Range, cl As Range
Static OldData As Variant
Application.EnableEvents = False
Set rng = Me.Range("J5:J36,L5:L36,N5:N36,P5:P36,R5:R36,T5:T36,V5:V36,X5:X36,Z5:Z36,ab5:ab36,AD5:AD36")
If IsEmpty(OldData) Then
OldData = rng.Value
End If
For Each cl In rng.Cells
If Len(cl) = 0 Then
cl.Offset(0, -1).ClearContents
Else
If cl.Value <> OldData(cl.Row - rng.Row + 1, 1) Then
With cl.Offset(0, -1)
.NumberFormat = "m/d/yy h:mm:ss"
.Value = Now
End With
End If
End If
Next
OldData = rng.Value
Application.EnableEvents = True
End Sub

 

[shg]

Ah -- I see a bug, corrected below:

          If IsEmpty(avNew(iRow, iCol)) Then
            Cells(iRow + .Row - 1, iCol + .Column - 2).ClearContents
          ElseIf avNew(iRow, iCol) <> avOld(iRow, iCol) Then
            Cells(iRow + .Row - 1, iCol + .Column - 2) = Now()
          End If

 

[terrib54]

I just tried it and it did not post a date in the column to the left.

 

[terrib54]

Tried it with the bug fix. It works. Thank you more than you can imagine. I learned a lot. Thanks.

 

[shg]

You're welcome.