Looking for a way to loop this
Results 1 to 8 of 8

Thread: Looking for a way to loop this

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Looking for a way to loop this

    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 by Johnzea; Jun 14th, 2019 at 07:26 PM.

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    456
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a way to loop this

    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 by kweaver; Jun 14th, 2019 at 08:06 PM.

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a way to loop this

    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 by Johnzea; Jun 14th, 2019 at 08:23 PM.

  4. #4
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    34,963
    Post Thanks / Like
    Mentioned
    91 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Looking for a way to loop this

    Quote Originally Posted by Johnzea View Post
    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?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    456
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a way to loop this

    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 by kweaver; Jun 14th, 2019 at 08:50 PM.

  6. #6
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,243
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Looking for a way to loop this

    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 by DanteAmor; Jun 14th, 2019 at 08:49 PM.
    Regards Dante Amor

  7. #7
    New Member
    Join Date
    Apr 2019
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Looking for a way to loop this

    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

  8. #8
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    5,243
    Post Thanks / Like
    Mentioned
    49 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Looking for a way to loop this

    Quote Originally Posted by Johnzea View Post
    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.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •