relatetive reference-to perform a series of cal and return result
 

I have set of data in column A (A1 to A20) for each cell data (Ai), I do a
series of calculation in another tab (sheet 2) and I bring the final result
to column B (Bi). The procedure in sheet 2 is same for all inputs. I want to
write a macro to repeat the procedure for me for A2 to A20.
I wrote the Macro but when I run it for A2, it performs with data one row
below my desired row.
I think my problem is with relative references, but I can't figure it out.
Any help is highly appreciated.

Here is the simplified version of What I want to do:

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+2"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[2]"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+3"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1"
Range("A3").Select
Sheets("Sheet2").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[1]"
Range("B2").Select
End Sub

relatetive reference-to perform a series of cal and return result
 

Some cleanup first

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("B1").FormulaR1C1 = "=RC[-1]+1" '=A1+1
Range("C1").FormulaR1C1 = "=RC[-1]+2" '=B1+2
Range("A2").FormulaR1C1 = "=R[-1]C[2]" '= C1
Range("B2").FormulaR1C1 = "=RC[-1]+3" '=A2+3
Range("C2")..FormulaR1C1 = "=RC[-1]*1" '=B1*1 (why *1?)
Range("A3").Select
Sheets("Sheet2").Select
Range("B1").FormulaR1C1 = "=Sheet1!R[1]C[1]" '=Sheet1!C2
Range("B2").Select
End Sub

Without knowing the final expected result, I'm not sure how to help any more.

"Khoshravan" wrote:

I have set of data in column A (A1 to A20) for each cell data (Ai), I do a
series of calculation in another tab (sheet 2) and I bring the final result
to column B (Bi). The procedure in sheet 2 is same for all inputs. I want to
write a macro to repeat the procedure for me for A2 to A20.
I wrote the Macro but when I run it for A2, it performs with data one row
below my desired row.
I think my problem is with relative references, but I can't figure it out.
Any help is highly appreciated.

Here is the simplified version of What I want to do:

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+2"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[2]"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+3"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1"
Range("A3").Select
Sheets("Sheet2").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[1]"
Range("B2").Select
End Sub

relatetive reference-to perform a series of cal and return res
 

Dear Barb
Thanks for your reply. The point is that I want to repeat this procedure for
20 cell values stored in A1 to A20 and write the results in B1 to B20.
Multiplication by one (*1) is only an example to show the sequence of
calculation I want to do (actual calculation is some other commands). You can
ignore it.
I want to know the structure of loop command to cycle properly among A1 to
A20 values as an input for calculation and
how to use relative reference or absolute one to cycle between A1 to A20.


"Barb Reinhardt" wrote:

Some cleanup first

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("B1").FormulaR1C1 = "=RC[-1]+1" '=A1+1
Range("C1").FormulaR1C1 = "=RC[-1]+2" '=B1+2
Range("A2").FormulaR1C1 = "=R[-1]C[2]" '= C1
Range("B2").FormulaR1C1 = "=RC[-1]+3" '=A2+3
Range("C2")..FormulaR1C1 = "=RC[-1]*1" '=B1*1 (why *1?)
Range("A3").Select
Sheets("Sheet2").Select
Range("B1").FormulaR1C1 = "=Sheet1!R[1]C[1]" '=Sheet1!C2
Range("B2").Select
End Sub

Without knowing the final expected result, I'm not sure how to help any more.

"Khoshravan" wrote:

I have set of data in column A (A1 to A20) for each cell data (Ai), I do a
series of calculation in another tab (sheet 2) and I bring the final result
to column B (Bi). The procedure in sheet 2 is same for all inputs. I want to
write a macro to repeat the procedure for me for A2 to A20.
I wrote the Macro but when I run it for A2, it performs with data one row
below my desired row.
I think my problem is with relative references, but I can't figure it out.
Any help is highly appreciated.

Here is the simplified version of What I want to do:

Sheets("Sheet1").Select
ActiveCell.FormulaR1C1 = "=Sheet2!RC"
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+1"
Range("C1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+2"
Range("A2").Select
ActiveCell.FormulaR1C1 = "=R[-1]C[2]"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]+3"
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1"
Range("A3").Select
Sheets("Sheet2").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[1]C[1]"
Range("B2").Select
End Sub