| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
loop in range
Thanks for your solutionOgilvy! but am not able to make the macro work. And
thanks to all you programmers who has helpt me with my questions. (It's not easy to be an Copy and Paste Programmer!) My problem is that I want to calculate every cell to the right from "RngMdDp1", based on the value on every cell in "RngMdDp1"That meens cells(O2:O115). compare to the value in cell (I3) Example: The Cell that is used to see witch calculation to use Cell ("I3") value = 1358 Example of the breaking point I wont in the ("RngMdDp1") Cell ("O55") value = 1355. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O56") value = 1357. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O57") value = 1359. Then it uses the second Calculation. "=(RC[-1])" Cell ("O57") value = 1361. Then it uses the second Calculation. "=(RC[-1])" I hope that I have explain so you anderstand my problems. Below is some of the macro I have used. That dont work. ----------------------------------------------------------------------------------- Sub DrillPipe1() Dim c as Range For Each c In Range("RngMdDp1") If c.Value < Range("I3") Then c.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else c.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" End if Next c End Sub --------------------------------------------------------------------------------- Sub DrillPipe1() For Each cell_in_loop In Range("RngMdDp1") If (cell_in_loop.Value Range("I3").Value) Then cell_in_loop.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else cell_in_loop.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" Exit For End If Next cell_in_loop End Sub Thanks for you'rs time Aksel |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop in range
The code I gave you does just that.
The second set of code you show does the opposite. So saying you can't make the macro work when it does what you describe, is less than helpful. I set up a sheet with your numbers and my code produced this: in R1C1 Notation O55: 1355 P55: =(RC[-1]*RC[-2]) O56: 1357 P56: =(RC[-1]*RC[-2]) O57: 1359 P57: =(RC[-1]) O58: 1361 P58: =(RC[-1]) That matches what you show exactly. Sub DrillPipe1() Dim c As Range For Each c In Range("RngMdDp1") If c.Value < Range("I3") Then c.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else c.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" End If Next c End Sub -- Regards, Tom Ogilvy "Aksel Børve" wrote in message ... Thanks for your solutionOgilvy! but am not able to make the macro work. And thanks to all you programmers who has helpt me with my questions. (It's not easy to be an Copy and Paste Programmer!) My problem is that I want to calculate every cell to the right from "RngMdDp1", based on the value on every cell in "RngMdDp1"That meens cells(O2:O115). compare to the value in cell (I3) Example: The Cell that is used to see witch calculation to use Cell ("I3") value = 1358 Example of the breaking point I wont in the ("RngMdDp1") Cell ("O55") value = 1355. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O56") value = 1357. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O57") value = 1359. Then it uses the second Calculation. "=(RC[-1])" Cell ("O57") value = 1361. Then it uses the second Calculation. "=(RC[-1])" I hope that I have explain so you anderstand my problems. Below is some of the macro I have used. That dont work. -------------------------------------------------------------------------- --------- Sub DrillPipe1() Dim c as Range For Each c In Range("RngMdDp1") If c.Value < Range("I3") Then c.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else c.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" End if Next c End Sub -------------------------------------------------------------------------- ------- Sub DrillPipe1() For Each cell_in_loop In Range("RngMdDp1") If (cell_in_loop.Value Range("I3").Value) Then cell_in_loop.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else cell_in_loop.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" Exit For End If Next cell_in_loop End Sub Thanks for you'rs time Aksel |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop in range
Can I mail this file to anyone? Can't find the solution.
Aksel "Tom Ogilvy" skrev i melding ... The code I gave you does just that. The second set of code you show does the opposite. So saying you can't make the macro work when it does what you describe, is less than helpful. I set up a sheet with your numbers and my code produced this: in R1C1 Notation O55: 1355 P55: =(RC[-1]*RC[-2]) O56: 1357 P56: =(RC[-1]*RC[-2]) O57: 1359 P57: =(RC[-1]) O58: 1361 P58: =(RC[-1]) That matches what you show exactly. Sub DrillPipe1() Dim c As Range For Each c In Range("RngMdDp1") If c.Value < Range("I3") Then c.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else c.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" End If Next c End Sub -- Regards, Tom Ogilvy "Aksel Børve" wrote in message ... Thanks for your solutionOgilvy! but am not able to make the macro work. And thanks to all you programmers who has helpt me with my questions. (It's not easy to be an Copy and Paste Programmer!) My problem is that I want to calculate every cell to the right from "RngMdDp1", based on the value on every cell in "RngMdDp1"That meens cells(O2:O115). compare to the value in cell (I3) Example: The Cell that is used to see witch calculation to use Cell ("I3") value = 1358 Example of the breaking point I wont in the ("RngMdDp1") Cell ("O55") value = 1355. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O56") value = 1357. Then it uses the first Calculation. "=(RC[-1]*RC[-2])" Cell ("O57") value = 1359. Then it uses the second Calculation. "=(RC[-1])" Cell ("O57") value = 1361. Then it uses the second Calculation. "=(RC[-1])" I hope that I have explain so you anderstand my problems. Below is some of the macro I have used. That dont work. -------------------------------------------------------------------------- --------- Sub DrillPipe1() Dim c as Range For Each c In Range("RngMdDp1") If c.Value < Range("I3") Then c.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else c.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" End if Next c End Sub -------------------------------------------------------------------------- ------- Sub DrillPipe1() For Each cell_in_loop In Range("RngMdDp1") If (cell_in_loop.Value Range("I3").Value) Then cell_in_loop.Offset(0, 1).FormulaR1C1 = _ "=(RC[-1]*RC[-2])" Else cell_in_loop.Offset(0, 1).FormulaR1C1 = "=(RC[-1])" Exit For End If Next cell_in_loop End Sub Thanks for you'rs time Aksel |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
loop in range
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| reset the range of FOR loop | Excel Discussion (Misc queries) | |||
| Loop in a Range | Excel Programming | |||
| Loop Through Highlighted Range | Excel Programming | |||
| Loop through a range | Excel Programming | |||
| using For each loop to populate a range | Excel Programming | |||
Linear Mode
