+ Reply to Thread
Results 1 to 5 of 5

Macro goes into an infinite loop

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-28-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    101

    Macro goes into an infinite loop

    There are two sheets in the workbook. First sheet has certain values in a number of rows in a column. The second sheet also has values in each row in repeating patterns in a column. Like this:

       Sheet1          Sheet2
            A                   A
    1  AAAA          gre1111assdf
    2  BBBB          gfd2222dfhh
    3  CCCC         jytjtu3333dgrt
    4  DDDD         yufg4444fbth
    5  EEEE          sdfs5555gfd
    The macro is supposed to go through each row in Sheet2 per each row in the first sheet; assign a certain value to a variable when any given row in Sheet2 includes some other certain values; compare this variable's value to the row value in Sheet1 and quit once this process has been repeated for 4 rows in Sheet1:

    Sub GetWpFromXMLs()
    Application.ScreenUpdating = False
    
        Dim sRange As Range '// "source" Range ie. Sheet2
        Dim dRange As Range '// "destination" Range ie. Sheet1
    
        dRow = 0 '// "destination" Row  ie. Sheet1
        dCol = 2 '// "destination" Column ie. Sheet1
        sRow = 0 '// "source" Row ie. Sheet2
        
        Do
        Set dRange = ActiveSheet.Range("A1").Offset(dRow, 0)
        Set sRange = Sheets("Sheet2").Range("A1").Offset(sRow, 0)
    
        If InStr(1, sRange, "1111") > 0 Then critName = "AAAA" '// "AAAA" is the value of A1 in Sheet1
        If InStr(1, sRange, "2222") > 0 Then critName = "BBBB" '// "BBBB" is the value of A2 in Sheet1
        If InStr(1, sRange, "3333") > 0 Then critName = "CCCC" '// "CCCC" is the value of A3 in Sheet1
        If InStr(1, sRange, "4444") > 0 Then critName = "DDDD" '// "DDDD" is the value of A4 in Sheet1
        If InStr(1, sRange, "5555") > 0 Then critName = "EEEE" '// "EEEE" is the value of A5 in Sheet1
    
        If dRange.Value = critName Then '// if the value extracted from the current Row in Sheet2 (sRange) matches the value of the current row in Sheet1 (dRange)
            MsgBox critName '// show what value was extracted
            dRow = dRow + 1 '// since current row in Sheet1 was taken care of, move onto the next row for the next loop
            sRow = 0 '// start over from the first row in Sheet2 in the next loop
        Else
            sRow = sRow + 1 '// since the current row in Sheet2 didn't match the current row in Sheet1, move onto the next row in Sheet2
        End If
    
        Loop Until dRow = 4 '// stop once first 3 rows in Sheet1 are taken care of
    
    Application.ScreenUpdating = True
    End Sub
    This is supposed to stop once the match for the 3rd row in Sheet1 is found in Sheet2 but it only happens for the 1st row in Sheet1 and then goes into infinite loop.

    I am very confused. Supposed to be that once a match for the first row of Sheet1 is made, dRow is incremented by 1 and thus looking for a match in for the second row of Sheet1 while sRow is reset to 0 so it starts from the first row of Sheet2 so as not to miss any rows. What is wrong here?
    Last edited by excelforum123; 10-21-2010 at 10:09 AM. Reason: See my last post ITT

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,019

    Re: Macro goes into an infinite loop

    It didn't go into an infinite loop for me. I displayed "AAAA", "BBBB", "CCCC", "DDDD" and finished.

    I stepped through with F8 and then just let it run (F5).

    Not sure what it's trying to achieve but it doesn't loop (more than it should)

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Macro goes into an infinite loop

    Sub GetWpFromXMLs()
      sq=sheet1.cells(1,1).resize(4)
      sn=sheet2.cells(1,1).resize(4)
    
      for j=1 to 4
        if switch(instr(sn(j,1),"1111")>0,"AAAA",instr(sn(j,1),"2222")>0,"BBBB",instr(sn(j,1),"3333")>0,"CCCC",instr(sn(j,1),"4444")>0,"DDDD"),instr(sn(j,1),"5555")>0,"EEEE")=sq(j,1) exit for 
      next
      if j<5 then msgbox sn(j,1) & " matches " & sq(j,1)
    End Sub
    Last edited by snb; 10-14-2010 at 01:15 PM.



  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    48,019

    Re: Macro goes into an infinite loop

    What is interesting is, if you change the first search string from 1111 to 1112 so that it doesn't match, it never goes through the part of the code which increments dRow.

    The implication is that it isn't finding what it is looking for.

    You could try setting the fourth parameter in INSTR

    Regards

  5. #5
    Forum Contributor
    Join Date
    06-28-2010
    Location
    London, England
    MS-Off Ver
    Excel 2013
    Posts
    101

    Re: Macro goes into an infinite loop

    Thanks. I don't know why it was infinitely looping for me. The same script started working properly without any changes. Weird.

    Also, thank you, snb. Novel suggestion, one I should remember but not for this particular example, as the actual has about 200 such entries going through the same procedure.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1