VBA syntax for variables in cell functions

[lejari7000]

Hi all, I am trying to insert this indirect formula and have it copied over multiple rows but obviously I can't drag it down or each cell references the exact same cell as opposed to the one underneath it. The problem appears to be that I don't know how to properly insert the rnum variable into this function. Any ideas?

Sub rform()
Dim rnum As Integer
rnum = 14
For r = 52 To 300


Cells(r, 1).Select
ActiveCell.FormulaR1C1 = "=INDIRECT( ""'Trading Platform'!A['&rnum&']"")"
rnum = rnum + 1
Next
End Sub

 

[shg]

Does this do what you want?

Sub rform()
    Range("A52:A300").Formula = "=INDIRECT('Trading Platform'!A14)"
End Sub

 

[Rick Rothstein]

Hi all, I am trying to insert this indirect formula and have it copied over multiple rows but obviously I can't drag it down or each cell references the exact same cell as opposed to the one underneath it. The problem appears to be that I don't know how to properly insert the rnum variable into this function. Any ideas?

Sub rform()
Dim rnum As Integer
rnum = 14
For r = 52 To 300
Cells(r, 1).Select
ActiveCell.FormulaR1C1 = "=INDIRECT( ""'Trading Platform'!A['&rnum&']"")"
rnum = rnum + 1
Next
End Sub

Change the highlighted line to this...

ActiveCell.FormulaR1C1 = "=INDIRECT( ""'Trading Platform'!A" & rnum & """)"

 

[lejari7000]

Not quite, I want to make the 14 part of A14 a variable so that I can insert this function in a loop. the end result being cell A52 has the function =INDIRECT('Trading Platform'!A14) and cell 53 is equal to =INDIRECT('Trading Platform'!A15), the next cell having A16 etc. So if the numerical row number were a variable in that function I think I could easily achieve this. Thanks for your help btw

 

[shg]

Not quite, ...

Did you try it?

 

[lejari7000]

Oh super fantastic! Thanks you so much worked like a charm :D

 

[Rick Rothstein]

Oh super fantastic! Thanks you so much worked like a charm :D

You should quote the messages you reply to when more than one person has answered you (delete non-relevant text though to keep the quote as short as possible) so we will know who you are replying to. The threaded view seems to show you are thanking me... so, you are welcome. However, my post was more of a "this is what the syntax looks like when integrating a variable into a text constant (text with quote marks)... you concatentate the two together rather than just embed one into the other. However, you should try shg's code... it works far more efficiently than yours. Note, in case you missed it... shg's code is a complete replacement for your code, not an amendment to it.

 

[shg]

You're welcome, good luck.