Insert formula to range of rows (Variable) from macro - debug help

[fishy]

I am getting a debug error 'Compile Error, Expected: end of statement' for
the following formula insertion:

Set Rng = Worksheets("Calculate").Range("E2:E30")
Formula =
"=IF(ISBLANK(D3)=TRUE,"Blank",IF(D3>=$D$2+($D$2*0.05),"Over",IF(D3<=$D$2-($C$2*0.05),"Under","Pass")))"

Column B has a variable number of rows which I need to drag the formula down
for so am putting rows 2-30 as there will never be more than this. Is there a
better way of doing this?

Any help appreciated

 

[Jacob Skaria]

Try

Range("E2:E" & Cells(Rows.Count, "B").End(xlUp).Row).Formula = _
"=IF(ISBLANK(D3),""Blank"",IF(D3>=$D$2+($D$2*0.05),""Over""," & _
"IF(D3<=$D$2-($C$2*0.05),""Under"",""Pass"")))"

If this post helps click Yes

 

[Barb Reinhardt]

Try this

Dim Rng As Excel.Range
Dim myWS As Excel.Worksheet

Set myWS = Worksheets("Calculate")

Set Rng = myWS.Range("E2:E30")
Rng.Formula = _
"=IF(ISBLANK(D3)=TRUE,""Blank"",IF(D3>=$D$2+($D$2*0.05)," & _
"""Over"",IF(D3<=$D$2-($C$2*0.05),""Under"",""Pass"")))"

 

[Jacob Skaria]

Barb, on a different note; were you able to sort out the SaveAs Event