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:

kweaver

Well-known Member
Joined
May 8, 2018
Messages
735
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:

Johnzea

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,510
Office Version
2010
Platform
Windows
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

Well-known Member
Joined
May 8, 2018
Messages
735
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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:

Johnzea

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

Well-known Member
Joined
Dec 3, 2018
Messages
8,900
Office Version
2007
Platform
Windows
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.
 

Forum statistics

Threads
1,082,373
Messages
5,365,070
Members
400,822
Latest member
Aldebaran13

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top