Looking for a way to loop this

Johnzea

New Member
Joined
Apr 5, 2019
Messages
23
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....
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Try this:

Code:
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
 
Last edited:
Upvote 0
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
Sorry, I think this line is incorrect. Change

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

to

Code:
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.
 
Last edited:
Upvote 0
If in column E you have a value or a formula and you just want to leave the value.
This can work without loop:

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top