Inserting formula with vba into created column

[WxShady13]

I am trying to have vba automatically place a formula in a range of cells to determine if the training date in the column beside it will be valid for the entire year. I cannot get the formula to paste into the new column.

'Dim lastRow as Long
'lastRow = Worksheets("New Year").range("c" & rows.count).End(xlUp).Row'
'Worksheets("New Year").range("J3:J" & lastRow).NumberFormat = "mm/dd/yyyy"
'Worksheets("New Year").range("J3:J" & lastRow).Formula = "=If(G3>12/31/2019,G3,If(G3=""COMPLETED"",""COMPLETED"","" "")"

Why does this not insert like the other formulas I have in the worksheet?

 

[Fluff]

Does the formula work if you type in manually?

 

[WxShady13]

Yes. I wrote it manually prior to working on getting the code to auto insert.

 

[Fluff]

You sure about that?
You've got two ( but only one )

 

[WxShady13]

Hmmm...If the training date is 7/7/19 it will blank the cell, but it will retain COMPLETED, but now that you mention it the cell does not retain a date of say 5/5/2020. What am I missing?

 

[Norie]

That code looks fine to me, are you sure the formula(s) aren't getting inserted?

The only possible problem I can see is with the formula itself, specifically the expression in your IF function.

Something like G3>12/31/2019 will not do a date comparison, in fact it will compare G3 to the value 0.000191727, i.e. the result of 12 divided by 31 divided by 2019.

Try replacing the expression with G3>DATE(2019,12,31).

Worksheets("New Year").range("J3:J" & lastRow).Formula = "=If(G3>DATE(2019,12,31),G3,If(G3=""COMPLETED"",""COMPLETED"","" "")"

 

[WxShady13]

Thank you for the update to the formula....The column is highlighted down to the last row but the cells are empty. It is weird

 

[Fluff]

Try
"=If(G3>DATE(2019,12,31),G3,If(G3=""COMPLETED"",""COMPLETED"","" ""))"

 

[WxShady13]

Thank you again!!!

 

[Fluff]

You're welcome