Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return a cell reference as a result of an IF formula | Excel Discussion (Misc queries) | |||
How to return a cell reference as result of a formula | Excel Worksheet Functions | |||
Perform a task if result is a whole number | Excel Programming | |||
Advanced formula - Return result & Show Cell Reference of result | Excel Worksheet Functions | |||
return the column reference number of a function result | Excel Discussion (Misc queries) |