Newbie question on cell reference 2

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Worksheets("Sheet2").Range("A1:A10").Formula = _
"Sheet1!A1"

Since A1 is a relative reference, it will be adjusted in A2:A10.
 
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 :)
 
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 said:
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 :)
Click to expand...
 
Thx i'll test it in the next morning (now its 4:00am in Hong Kong).



Tom Ogilvy said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top