Looking for a way to loop this

[Johnzea]

This may be an easy one but I am just starting to learn all of this. below is a code that works great, only i would have to type it over and over again until i reach row 250 i want to loop through the E Column cell rows from 2 - 250 until it reaches the end. Any help would be greatly appreciated...


Range("E2").Select
ActiveCell.FormulaR1C1 = ActiveCell.Value
If Range("D2").Value > "0" Then
Range("N2").Formula = "=M2*D2"
Else
Range("N2").Formula = ""
End If
Range("B2").Value = Range("O2").Value


Range("E3").Select
ActiveCell.FormulaR1C1 = ActiveCell.Value
If Range("D3").Value > "0" Then
Range("N3").Formula = "=M3*D3"
Else
Range("N3").Formula = ""
End If
'Range("B3").Value = Range("O3").Value


Range("E4").Select
ActiveCell.FormulaR1C1 = ActiveCell.Value
If Range("D4").Value > "0" Then
Range("N4").Formula = "=M4*D4"
Else
Range("N4").Formula = ""
End If
'Range("B4").Value = Range("O4").Value

etc....


etc....



etc....

 

[kweaver]

Try this:

DIM LR As Long, i As Integer
LR = Cells(Rows.Count,"A").End(xlUp).Row
For i = 2 to LR
Range("E" & i).Select
    ActiveCell.FormulaR1C1 = ActiveCell.Value
     If Range("D" & i).Value > 0 Then    ' shouldn't this be a number 0 rather than text 0?
         Range("N" & i). = Range("M" & i)*Range("D" & i)
         Else
         Range("N" & i) = ""
         End If
         Range("B" & i) = Range("O" & i).Value
Next i

 

[Johnzea]

Works Great...It amazes me how simple you made it. I am learning from all this.... Thank You so much Kweaver for the help...

Johnzea

 

[Rick Rothstein]

Works Great...It amazes me how simple you made it. I am learning from all this.... Thank You so much Kweaver for the help...

I think the code may be able to be simplified a touch more, but in order to decide that, can you tell us what is in the cells of Column E?

 

[kweaver]

Sorry, I think this line is incorrect. Change

Range("N" & i). = Range("M" & i)*Range("D" & i)

to

Cells(i, 14) = Cells(i, 13) * Cells(i, 4)

I think that works correctly.

Actually, I just tested it after I posted it and the original works. DUH.

 

[DanteAmor]

If in column E you have a value or a formula and you just want to leave the value.
This can work without loop:

Sub test2()
    dim lr as long
    lr = Range("E" & Rows.Count).End(xlUp).Row
    Range("E2:E" & lr).Value = Range("E2:E" & lr).Value
    Range("N2:N" & lr).Value = "=if(D2>0,M2*D2,"""")"
    Range("B2:B" & lr).Value = Range("O2:O" & lr).Value
End Sub

 

[Johnzea]

Thank You all as all the recommendations are working, and I am getting used to how the coding works with your help. much appreciated with all this

 

[DanteAmor]

Thank You all as all the recommendations are working, and I am getting used to how the coding works with your help. much appreciated with all this

I'm glad to help you. Thanks for the feedback.