I have encounter some problem of my own as well

I want to use VBA to format my work sheets but i have problem making it work
Let say i want to:
1. link value from sheet1!a1 to sheet2!a1 using formul
2. offset by 1 ro
3. link value from sheet1!a2 to sheet2!a2 using formul
4. run 1,2,3 several time
5. offset by 3 ro

I tried to use "For Next" to acomplish this but i dont know how to do the actual formula part
ActiveCell.FormulaR1C1 = "=sheet1!R[Row_num]C[Stay]
How to i use the counter of the "For Next" to be insert in the Row_num? But I want the Column to stay

Please help me out Guru

Mface

I'm not quite sure I understand you, but the For Next loop works lik
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. - Piku

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

Worksheets("Sheet2").Range("A1:A10").Formula = _
"Sheet1!A1"

Since A1 is a relative reference, it will be adjusted in A2:A10.

--
Regards,
Tom Ogilvy

"Mfaces" wrote in message
...
I have encounter some problem of my own as well:

I want to use VBA to format my work sheets but i have problem making it
work.
Let say i want to:
1. link value from sheet1!a1 to sheet2!a1 using formula
2. offset by 1 row
3. link value from sheet1!a2 to sheet2!a2 using formula
4. run 1,2,3 several times
5. offset by 3 row

I tried to use "For Next" to acomplish this but i dont know how to do the
actual formula part.
ActiveCell.FormulaR1C1 = "=sheet1!R[Row_num]C[Stay]"
How to i use the counter of the "For Next" to be insert in the Row_num?
But I want the Column to stay.

Please help me out Guru.

Mfaces

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/

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/

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/