Adding a If criteria within a Do Loop

ks0ks · Jun 9th 2012

Hi,

I have a code that is working fine.

The code searches a sheet 'License PWC' for the word 'Renewal'. If the word is found, it copies the entire row of data to another sheet 'expiry due 60d'. The rows of data are populated to column 12.

Now, I would like to add in another feature. Column 13 is populated with "yes" and "no". If the word "renewal" is found, and if column 13 of that row is "no", only then it will transfer the data into sheet 'expiry due 60d'.

My code is as shown:

Code

With ThisWorkbook.Sheets("License PWC")
    
    lastrow = .Cells(25535, 1).End(xlUp).Row


        Set foundcell = .Cells.Find(what:="Renewal", LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlDown, MatchCase:=False)
        
        If Not foundcell Is Nothing Then
            firstadd = foundcell.Address
        End If
        
        m = ThisWorkbook.Sheets("expiry due 60d").Cells(25535, 1).End(xlUp).Row + 1
        
        
        Do Until foundcell Is Nothing
            Set foundcell = .Range("H:H").FindNext(after:=foundcell)
            expiredrow = foundcell.Row
            
'            If .Cells(expiredrow, 13) = "No" Then
            
                For i = 0 To 11
                    data(i) = .Cells(expiredrow, i + 1)
                    ThisWorkbook.Sheets("expiry due 60d").Cells(m, i + 1) = data(i)
                Next i
                
                m = m + 1
                
                If firstadd = foundcell.Address Then
                    Exit Do
                End If
            
'            End If
        Loop


End With

Display More

The code above works fine, without the added feature.
I have tried to add in that feature with a IF command (commented out in the above code). If I uncomment the If command, the code runs into an infinite loop.

Upon debugging, the following line is highlighted in yellow.

Code

expiredrow = foundcell.Row

How should I include that feature into my exisiting code?

Many thanks!

dangle · Jun 10th 2012

Re: Adding a If criteria within a Do Loop

It looks like you have wrapped your additional 'if' statement around your 'if ... exit do' statement. Therefore if there is a cell which does not meet the "No" condition, your sub won't be able to test the 'firstadd' condition and will go into the infinite loop. So you need to move the end of the additional 'if' statement so it does not cover the 'if ... exit do' statement.
Why not use 'autofilter' instead of doing this loops? I think this would be easier than the method you are using. Also, please provide sample data so that it is easier to advise.

Adding a If criteria within a Do Loop

Participate now!

Share