Using Variable in Cell Formula as part of For Next

I have 2 sheets, Sheet1 and Sheet2

I want to populate Sheet2 Column B with a formula which will reference Column B of sheet1 so that on each iteration of the For..Next, the cell being referenced in the formula will change.

So if i = 5, I'd like the cell to be populated with the formula
=Sheet1!B5


So I have the following code

Please Login or Register  to view this content.

However, something clearly isn't working right.


- Firstly, from what I can see, the FormulaR1C1 takes the numbers in R[] and C[] as relative offsets. So R[1] if I'm in row 1, would point to row 2. Is my understanding here correct? And if so, how to I get it to just point to row i based on the variable value?

- Secondly, there is something else wrong in what I've done in that the cells this macro is populating column B with the same formula in every cell (so each cell in column B is pointing to B13)

"=Sheet1!B13"

To get an R1C1 style reference to refer to the same row you're in, simply omit any number after the R. Also, taking the brackets off of the numbers you do use will make the references absolute.

R1 - always means row 1
R[1] - always means 1 row below where this formula is located
R[-1] - 1 row above
R - means "same as this row"

If you're looping through rows, you can just use the R by itself since it's rows you're dealing with, most likely the formula you're inserting is meant to apply to this row, yes?

SIDE NOTE:
In declaring variables, go ahead and get into the habit of declaring row variables as LONG, not INTEGER. Even in Excel 2003 there are more rows on a sheet than an INTEGER variable can hold, LONG is the safer choice for row/column variables.

Might be an idea to clarify how you're defining lastrow variables as I suspect you can achieve this without need for iteration.