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

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

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

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
---------------
Jacob Skaria


"fishy" wrote:

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

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

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"")))"

--
HTH,

Barb Reinhardt



"fishy" wrote:

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

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

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


"Barb Reinhardt" wrote:

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"")))"

--
HTH,

Barb Reinhardt



"fishy" wrote:

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