View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Khoshravan Khoshravan is offline
external usenet poster
 
Posts: 211
Default 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