Error handling in Do Until statements

vinyasa

New Member
Joined
Feb 6, 2013
Messages
30
Hey all

My code runs correctly assuming a few conditions are met. It loops through a range and continues until it finds a string. I want it to exit and show a message box if it doesn't find the string.

Right now, it runs the loop but doesn't get to the On Error statement if it doesn't find the string. If I put the On Error statement inside the loop, it stops after the first iteration. I have tried to think of a way to handle this by using another "InStr = 0" type statement, but I'm not seeing how that logic can work with a Do Until Loop.

Any advice?

HTML:
'Finds String to declare variable    
Range("B1").Select    
Do Until InStr(ActiveCell, "String") > 0        
    ActiveCell.Offset(1, 0).Select    
Loop   
 
On Error GoTo StringError        
Exit Sub    

StringError:        
    MsgBox ("String not found.  Please correctly label as 'String'.")
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
I would like to add that in the following code, if the String is found (i.e. there is no error), it will still go to the error handling.

HTML:
'Finds String to declare variable    
Range("B1").Select    
Do Until InStr(ActiveCell, "String") > 0        
    ActiveCell.Offset(1, 0).Select        
    On Error GoTo StringError    
Loop    

StringError:        
    MsgBox ("String not found.  Please correctly label as 'String'.")    
Exit Sub
 
Upvote 0
Looping through every possible row on your worksheet is extremely inefficient. You want to limit it to just the rows you have used.

Here is a little bit different way to accomplish what you are after:

Code:
Sub MySearch()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myCount As Long
    
'   Find last row in column B
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row

'   Loop through column B looking for first instance of string
    For myRow = 1 To myLastRow
        If InStr(Cells(myRow, "B"), "String") > 0 Then
            myCount = myCount + 1
            Cells(myRow, "B").Activate
            Exit For
        End If
    Next myRow
 
'   If string not found, return message box
    If myCount = 0 Then
        MsgBox ("String not found.  Please correctly label as 'String'.")
    End If
    
End Sub
 
Upvote 0
Thank you. I hadn't thought of finding the total number of rows to limit the loop. I also had no clue you can use Rows.Count to find the total rows. I had always hard coded it.

Thanks for the help. It was very informative!
 
Upvote 0
ADVERTISEMENT
Your welcome!

Just to clarify one thing, Rows.Count does not give your the total number of used rows on a sheet, it returns the total number of possible rows on a sheet.
What that does is take you down to the last possible row on a sheet, regardless of which version of Excel you are using.
It is the .End(xlUp).Row part that finds the last used row in that column.

Essentially, you are telling Excel to go to the last possible row in a column, and then go up until you find a non-empty row. That row is your last populated row in that column.
 
Upvote 0
The InStr command searches only if the exact words are found. Let's say that instead of just "String", we had "(1) String X", "(2) String Y", and so on. It currently returns 0, or false, since it isn't finding the exact text. Now I know that you can add a subtext to the InStr command, but since the main text changes, I'm not able to get it to run correctly.

Any advice?
 
Upvote 0
The InStr command searches only if the exact words are found
Not true. In our example, it will return any entry that has "String" as any part of it. So "(1) String X" would be returned.

However, InStr is context sensitive. So in your example, if you were searching for "String", it would find it. However, if you were searching for "string" or "STRING", it would not find it.
If you do not care about case and want all variations of "String" returned, make this modificiaton:
Code:
Sub MySearch()

    Dim myLastRow As Long
    Dim myRow As Long
    Dim myCount As Long
    
'   Find last row in column B
    myLastRow = Cells(Rows.Count, "B").End(xlUp).Row

'   Loop through column B looking for first instance of string
    For myRow = 1 To myLastRow
        If InStr(UCase(Cells(myRow, "B")), "STRING") > 0 Then
            myCount = myCount + 1
            Cells(myRow, "B").Activate
            Exit For
        End If
    Next myRow
 
'   If string not found, return message box
    If myCount = 0 Then
        MsgBox ("String not found.  Please correctly label as 'String'.")
    End If
    
End Sub
 
Upvote 0
I see. I think it isn't working because the IF statement ends upon the first instance of String and in this case I am looking for multiple instances and to place a number in the correct cell. I'll mess around with it later.

Thanks for the help Joe!
 
Upvote 0

Forum statistics

Threads
1,196,360
Messages
6,014,814
Members
441,847
Latest member
hw407

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top