Thx i'll test it in the next morning (now its 4:00am in Hong Kong).



"Tom Ogilvy" ¦b¶l¥ó
¤¤¼¶¼g...
For x = 1 to 10
Sheets("sheet2").Select
ActiveCell.FormulaR1C1 = "=sheet1!R[" & x & "]C[1]"
ActiveCell.Offset(1, 0).Range("A1").Select
Next x

But see my other post for a better solution.

--
Regards,
Tom Ogilvy


"Murphy Hui" wrote in message
...
Thanks for looking in my post

What i want is using the "x" variable for both counter and cell off set.

The following is for explaining purpose and its wrong, just want to get
the
question out.
Note that i want to insert a formula at sheet2 instead of value.
This way i can re-input value on sheet1 and it will appear on sheet2
without
running the marco again.

For x = 1 to 10
Sheets("sheet2").Select
ActiveCell.FormulaR1C1 = "=sheet1!R[x]C[1]"
ActiveCell.Offset(1, 0).Range("A1").Select
Next x

Please let me know if I further confuse you :)

"pikus" ¦b¶l¥ó
¤¤¼¶¼g...

I'm not quite sure I understand you, but the For Next loop works like
this:

For x = 1 To 10
var1 = Worksheets(1).Cells(x, 1).Value
Worksheets(2).Cells(x, 1).Value = var1
Next x

Let me know if that doesn't answer your question. - Pikus


---
~~ Message posted from http://www.ExcelForum.com/