Hi all,
I leveraged some code I found here in the forum. Very simple in appearance, but it always skips over my If statement if I step through the code even on dates that already exist.
Here's the setup. Workbook1 has about 600 cells with a Vlookup formula in column N. The Vlookup formula pulls its data from a column in Workbook2 which will return a date within a one year span in Workbook1 Column N once the column in Workbook2 is populated with a date within that same one year span. Inititially, the "date" column cells in Workbook2 are blank.
Once column N in Workbook1 displays a date, I want to change 3 cells in the same row as the newly appearing date to read "Y". I want the macro to automatically check for a date range within each cell in Workbook1 Column N and execute the 3 value changes in that same row as long as the date falls within the specified range.
Here's the leveraged code after I modiifed it to what I thought MIGHT work. I placed this code into the private module for worksheet in question in Workbook1. I used Excel date serial numbers in the code.
I suspect the problem is that I'm not including code to monitor when the formulas finally display a value that is not "blank". Perhaps the "previously blank" values need to be stored in a temporary array for comparison to any new values that may exist each time the date is updated in Workbook2.
BTW: the code above works in a Worksheet_Change sub if I manually enter a date into Workbook1 Column N.
I'm open to ideas and suggestions.
I leveraged some code I found here in the forum. Very simple in appearance, but it always skips over my If statement if I step through the code even on dates that already exist.
Here's the setup. Workbook1 has about 600 cells with a Vlookup formula in column N. The Vlookup formula pulls its data from a column in Workbook2 which will return a date within a one year span in Workbook1 Column N once the column in Workbook2 is populated with a date within that same one year span. Inititially, the "date" column cells in Workbook2 are blank.
Once column N in Workbook1 displays a date, I want to change 3 cells in the same row as the newly appearing date to read "Y". I want the macro to automatically check for a date range within each cell in Workbook1 Column N and execute the 3 value changes in that same row as long as the date falls within the specified range.
Here's the leveraged code after I modiifed it to what I thought MIGHT work. I placed this code into the private module for worksheet in question in Workbook1. I used Excel date serial numbers in the code.
Code:
Private Sub Worksheet_Calculate()
For Each c In Range("N3:N600")
'If cell value is within date range 11/1/2010 to 10/31/2011 enter If statement to changes other column values.
If c.Value >= "40483" And c.Value < "40848" Then
c.Offset(0, -7).Value = "Y"
c.Offset(0, -6).Value = "Y"
c.Offset(0, -5).Value = "Y"
End If
Next c
End SubI suspect the problem is that I'm not including code to monitor when the formulas finally display a value that is not "blank". Perhaps the "previously blank" values need to be stored in a temporary array for comparison to any new values that may exist each time the date is updated in Workbook2.
BTW: the code above works in a Worksheet_Change sub if I manually enter a date into Workbook1 Column N.
I'm open to ideas and suggestions.
...and it worked!
