Newbie question on cell reference 2

[Mfaces]

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

 

[pikus]

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

 

[Tom Ogilvy]

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

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

 

[Murphy Hui]

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

 

[Tom Ogilvy]

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

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

 

[Murphy Hui]

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

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

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

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