loop in range

A

Aksel Børve

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
 
T

Tom Ogilvy

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
 
A

Aksel Børve

Can I mail this file to anyone? Can't find the solution.
Aksel

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

Tom Ogilvy

(e-mail address removed)

--
Regards,
Tom Ogilvy

Aksel Børve said:
Can I mail this file to anyone? Can't find the solution.
Aksel

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop in a Range 2
COPY BY MACRO DOWN TO LAST VALUE CELL IN COLUMN "A" 1
FormulaR1C1 2
Why are the two codes below not getting the right formula for myworksheet event change formula? 2
Macro autofills formula when a cell value is changed 4
Auto-calculate after a cell is filled in - macro shouldn't be loop 5
Problem with line of code / syntax 4
Macro to search range for a portion of a specific value and if findit - then paste formula in anothe 1

Top