VBA code to autofill based on a date?

[three482]

Hi all.

I’m trying to add some code that will autofill cells based on the date in another cell.

The macro is currently set to newest file of the same name when ran. I have code that creates a new “Comments” column at the end (column G), but I want it to reference the Date column (column B) and autofill “Ignore” into column G if the date on that row is before 1/1/2020. Of the data is after 1/1/2020, leave blank.

For example, if the date in B1 is 12/1/2019, G1 would autofill “Ignore.” If the date in B2 is 2/1/2020, G2 would remain blank.

Thanks in advance!

 

[Joe4]

Why do you need VBA code? Wouldn't a simple IF formula suffice, i.e.

=IF(B1<DATE(2020,1,1),"Ignore","")

and copy down for all rows.

 

[three482]

Why do you need VBA code? Wouldn't a simple IF formula suffice, i.e.

=IF(B1<DATE(2020,1,1),"Ignore","")

and copy down for all rows.

Yeah, I’ve tried a formula very similar to that one, but it’s a new file that imported daily so the formula would need to be added every day since I have no way of adding it to the source file. Hence, I wanted to add it to the macro to include as it runs.

 

[Joe4]

Here is VBA code that will populate column G with the formula you need in all row, down to the last row in column B with data.

'   Find last row in column B with data
    Dim lr As Long
    lr = Cells(Rows.Count, "B").End(xlUp).Row
    
'   Populate column G with formula
    Range("G1:G" & lr).FormulaR1C1 = "=IF(RC[-5]<DATE(2020,1,1),""Ignore"","""")"