Variable cell in formula

[cdhoughton]

I have a VB script that adds a FormulaR1C1 value to a cell.

However, I need to set a part of the formula to a different value in another cell:
----------------------
No = Sheets("Management Information").Range("B48").Value + 7

Sheets("Sheet1").Range("A1").FormulaR1C1 = "=-INDIRECT(""'""&SheetName&""!$C$" & No & """)"
----------------------

At the moment the value is coming out at 7 and not recognising the first bit of the formula?!?

Any ideas?

 

[earlyd]

make sure the value in B48 is numeric. Didn't know NO could be used for a variable - would have thought it was reserved?

 

[Andrew Poulsom]

Your code gave me a #NAME error. Is this what you want?

Sheets("Sheet1").Range("A1").Formula = "=-INDIRECT('" & SheetName & "'!$C$" & No & ")"

When using A1 style references you should use the Formula property, not the FormulaR1C1 property.

 

[cdhoughton]

Cheers folks, turned out it was the R1C1 that was the problem!

Thanks for your help!