problems using FormulaR1C1

J

June Macleod

I am trying to set up a formula for several cells and am having a problem
because extra single quotes are appearing.

I use the following code:

Set wc = Worksheets("Cost")

startcol = 14
n = 0
wdarow = 24
For m = 1 To 80 Step 4
n = n + 1
wc.Range(Cells(8, startcol + m), Cells(8, startcol + m)).FormulaR1C1
= "=CB.GetForePercentFN('Timing Analysis'!AE" & wdarow & ",pValue)"
wdarow = wdarow + 7
If n = 2 Then
wdarow = wdarow + 9
n = 0
End If
Next m

and the result in the cell is:

=CB.GetForePercentFN('Timing Analysis'!'AE31',pValue)

If I remove the single quotes around AE31 it works fine but what I don't
understand is why these quotes are appearing in the first place.

Thanks

Neme
 
J

JE McGimpsey

If you're using R1C1, then you need to use R1C1 references, not A1-style.

Try:

wc.Range(Cells(8, startcol + m), Cells(8, startcol + _
m)).FormulaR1C1= _
"=CB.GetForePercentFN('Timing Analysis'!R" & wdarow & _
"C31,pValue)"
 
Top