ActiveCell.FormulaR1C1 referencing a moving target.
 

j = Activecell.Column
For i = 2 To 18 Step 8
Cells(Activecll.Row, j).FormulaR1C1 = "=SheetName!R[" & i & "]C"
j = j + 1
Next i

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"colin919" wrote in message
...
My current situation is:

ActiveCell.FormulaR1C1 = "=SheetName!R[2]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SheetName!R[10]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SheetName!R[18]C"
etc......

I would like to replace the row with an integer increasing by 8 therefore
creating a small loop. VB at the moment does not seem to understand my
request. Is there an easier way to do this? Please help!

ActiveCell.FormulaR1C1 referencing a moving target.
 

Are you sure it works <G.? Looking again it seems to me I have rows and
columns mixed up. Assuming it does

With Worksheets("SheetName")
j = Activecell.Column
For i = 2 To .Cells(.Rows.Count, j).End(xlUp).Row Step 8
.Cells(Activecell.Row, j).FormulaR1C1 = "=SheetName!R[" & i &
"]C"
j = j + 1
Next i
End With

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"colin919" wrote in message
...
Works great!
But it created another question. How do I word the "Do while loop" to run
until the data in the "Sheetname" cell is emptycell? My syntax does not
seem
to be working.

"Bob Phillips" wrote:

j = Activecell.Column
For i = 2 To 18 Step 8
Cells(Activecll.Row, j).FormulaR1C1 = "=SheetName!R[" & i & "]C"
j = j + 1
Next i

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"colin919" wrote in message
...
My current situation is:

ActiveCell.FormulaR1C1 = "=SheetName!R[2]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SheetName!R[10]C"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SheetName!R[18]C"
etc......

I would like to replace the row with an integer increasing by 8
therefore
creating a small loop. VB at the moment does not seem to understand my
request. Is there an easier way to do this? Please help!