I have an excel sheet developed by a vendor that I'm stuck with the current functionality as a short term solution. The users workflow have them pull 4 separate sets of data which they are copying and pasting into a different spreadsheet where they append the data to previous days' pricing for trend analysis. That macro is built looking for the first blank row in column A, simple enough.
However, they then add the current day's date to this other spreadsheet as an additional column to do that trending. I then entered the following logic that states if column A is populated, populate Column T with today's date statically (Today and Now don't work because I need it to stay at that date when it is refreshed).
This has proven to be inconsistent where it populates the date properly sometimes and populates nothing the rest of the time. I've also seen it populate the date properly on the first copy and paste but not the other 3. I can't put my finger on what is causing the inconsistency. I thought the count was causing issues originally but am stuck right now. Sorry for the long post.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myIntersect As Range
Dim myCell As Range
Dim RngToInspect As Range
With Me 'the sheet that owns the code
Set RngToInspect = .Range("A2", .Cells(.Rows.Count, "A"))
End With
Set myIntersect = Intersect(Target, RngToInspect)
If myIntersect Is Nothing Then
Exit Sub 'not in that range
End If
Application.EnableEvents = False
For Each myCell In myIntersect.Cells
With Me.Cells(myCell.row, "T")
.NumberFormat = "mm/dd/yyyy"
.Value = Date
End With
Next myCell
Application.EnableEvents = False
End Sub
Bookmarks