Place formula in Cell - error 1004

[ERLoft]

Thought this was simple, but keep getting errors.

Pasting into N1 works fine. So does G1, but the line pasting into G2 causes a 1004. Any ideas why?

I also tried using FormulaR1C1 and assigning the formula (=IF(A2=$N$1,"",IF(G1="","",A2))) to a string variable, then pasting that. All of these error out. Manually typing the formula in works fine.

Sheets("Data1").Range("N1").Value = "****PERIOD TO DATE****"
Sheets("Data1").Activate
Range("G1").Value = "=IF(A1="","",A1)"
Range("G2").Value = "=IF(A2=$N$1,"",IF(G1="","",A2))"
Range("G2").Copy
Range("G3:G1000").PasteSpecial xlPasteFormulas

 

[Joe4]

You need to double-up the double-quotes, as they are also text qualifiers in VBA.
So this line:

Range("G2").Value = "=IF(A2=$N$1,"",IF(G1="","",A2))"

need to look like:

Range("G2").Value = "=IF(A2=$N$1,"""",IF(G1="","",A2))"

Also, while that works, when entering formulas, it is more correct to do:

Range("G2").Formula = "=IF(A2=$N$1,"""",IF(G1="","",A2))"

 

[ERLoft]

You need to double-up the double-quotes, as they are also text qualifiers in VBA.
So this line:

Range("G2").Value = "=IF(A2=$N$1,"",IF(G1="","",A2))"

need to look like:

Range("G2").Value = "=IF(A2=$N$1,"""",IF(G1="","",A2))"

Also, while that works, when entering formulas, it is more correct to do:

Range("G2").Formula = "=IF(A2=$N$1,"""",IF(G1="","",A2))"

Thanks so much Joe4! I completely brain farted the quad quotes.

 

[DanteAmor]

You can use the formula in the entire range you want:

Change your code to the following:

Sub formula_1()
  With Sheets("Data1")
    .Range("N1").Value = "****PERIOD TO DATE****"
    .Range("G1").Value = "=IF(A1="","",A1)"
    .Range("G2:G1000").Formula = "=IF(A2=$N$1,"""",IF(G1="","",A2))"
  End With
End Sub

 

[Joe4]

You are welcome.
Glad we were able to help!