Consulting

Results 1 to 8 of 8

Thread: Solved: Loop not running correctly

  1. #1
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location

    Solved: Loop not running correctly

    I am trying to run the following. It is supposed to run through to the end of the list of data. However when I run it, it seems to stop on the first cell (The first cell meets the condition and I get a value pasted to L2). It doesnt seem to keep running through the data after that though. I can't seem to figure out what is wrong with it.... Any help appreciated...


    [VBA]
    Option Explicit

    Sub Tokyo_Time()
    Dim i As Long, LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then

    Do
    Range("L2").Value = Range("O2").Value
    Loop Until Range("C" & i).Value = ""
    End If
    Next i

    End Sub



    [/VBA]

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. I always like to qualify my ranges: Worksheet("MyData").Range ("A1")

    2. If you're going to use a For i = 2 to LastRow loop, my choice/opinion/personal style would be to use .Cells(i,3).Value

    3. I don't understand the Do/Loop Until. It seems like you're just putting O2 into L2 a lot of times

    4. Got a small sample you can post, along with the Before and After?

    Paul

  3. #3
    VBAX Mentor
    Joined
    Apr 2009
    Location
    Kingsbury
    Posts
    423
    Location
    Your code goes into a endless loop
    try this
    [VBA]
    Sub Tokyo_Time()
    Dim i As Long, LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then
    Range("L2").Value = Range("O2").Value
    End If
    If Range("c" & i).Value = "" Then Exit Sub
    Next i

    End Sub
    [/VBA]

  4. #4
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    It looks like what that is doing is
    "If any cell in column C is between the values in P2 and P3 then set L2 to the value in O2."

    Not only is the Do Loop infinite, but no looping is needed at all
    [VBA]With Range("L2")
    .FormulaR1C1 = "=IF(SUMPRODUCT(--(R2C16<=C3)*(C3<=R3C16)),R2C15,""" & .Value & """)"
    .Value = .Value
    End With[/VBA]

  5. #5
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    Hi Guys, thanks for the contributions. After reviewing them I realized the glaring mistake in my code......

    [VBA]
    Range("L2").Value = Range("O2").Value
    [/VBA] Should read
    [VBA] Range("L" & i).Value = Range("O2").Value [/VBA]


    I have modified the code to the following and it now works exactly as expected!!

    Thank you
    [VBA]
    Option Explicit

    Sub Tokyo_Time()
    Dim i As Long, LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LastRow
    If Range("C" & i).Value >= Range("P2").Value And Range("C" & i).Value <= Range("P3").Value Then
    Range("L" & i).Value = Range("O2").Value
    End If
    Next i

    End Sub


    [/VBA]

  6. #6
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    You can use mikerickson's non-looping approach, especially if Column L is originally blank as below:
    [VBA]Sub Tokyo_Time2()
    With Range("L2:L" & Range("A" & Rows.Count).End(xlUp).Row)
    .FormulaR1C1 = "=IF(AND(RC[-9]>=R2C16,RC[-9]<=R3C16),R2C15,"""")"
    .Value = .Value
    End With
    End Sub
    [/VBA]
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  7. #7
    VBAX Tutor
    Joined
    Sep 2008
    Posts
    213
    Location
    Thanks shriv ,another way to approach it that I didnt know of.

  8. #8
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    Nice that it works for you. But it was mickericson who pointed it out...so I tried to understand a few things about it here:
    http://www.vbaexpress.com/forum/showthread.php?t=39788
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

Posting Permissions

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