How to use a variable placeholder to index rows? i.e. RyC7

[KS_user]

Each time this loop executes, I want the formula 'ActionLevel' to move down another row. So I want to use the y variable as the row index, but this won't work. VBA doesn't give me an error, but the function itself doesn't work (on the cells it's supposed to calculate, a #VALUE appears)

x = 7
F = 17
y = 10

Do While x < F
Cells(i, j) = x
Cells(i, k).FormulaR1C1 = "=EDS_Value(Esc_CPD_pH, RC[-1])"
Cells(i, l).FormulaR1C1 = "=ActionLevel(RC[-1], Limits_Page!RyC7:R17C16, 9)"
x = x + 0.020833338
y=y+1
i = i + 1
count = count + 1
Loop

Thanks!!

 

[KS_user]

test with quote

test again

 

[Rick Rothstein]

Cells(i, l).FormulaR1C1 = "=ActionLevel(RC[-1], Limits_Page!RyC7:R17C16, 9)"

Try the above line of code like this instead...

Cells(i, l).FormulaR1C1 = "=ActionLevel(RC[-1], Limits_Page!R" & y & "C7:R17C16, 9)"

The way you had it, the y was inside a quoted text constant... VB cannot see that as anything more than a single letter character that way... you have to concatenate the variable into the text so that VB can see it is a variable. Think about what you did originally... if that would have worked, then how would you ever have been able to include the letter "y", like in the word "day" for instance, without VB changing it as well?