Error Handler Not Working

[karabiner8]

I've adapted a bit of code I found on the web to search for text in two adjacent columns where only numbers should be. It works fine when I test it. But now I want to check two more columns that are not contiguous with the first two columns I checked and so on for a total of three groups of two adjacent columns.

I tried a For loop but got errors so I thought I'd just do a brute force method and copy the code down three times (once for each group of two adjacent columns) and just change the range that is being looked at but the Err.Clear statement doesn't seem to be doing its job.

I've included the code below to check just the first two groups of adjacent cells. The code works for all scenarios accept one: if none of the target columns contain any text at all it bombs out with the error Run-time error '1004': No cells were found. after reporting that the first group of columns has no text in it. The line highlighted when I hit debug is:

Set oNOCells = Sheets("SG_Tracker").Range("BC8:BD" & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)

The full code is:

Sub FindCellsWithText()
'adapted from http://vbadud.blogspot.com/2008_06_01_archive.html
Dim lLastRow As Long
Dim oNOCells
'Find last row
    lLastRow = Range("W" & Rows.Count).End(xlUp).Row
 
'+++++++++++++++check first range
    On Error GoTo Err_Hdlr1
    Set oNOCells = Sheets("SG_Tracker").Range("AW8:AX" & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
 
    For Each ocell In oNOCells
        MsgBox "There is text in cell " & ocell.Address & vbCrLf & _
        "make a note of it and correct it.", vbOKOnly, "Error!!!"
    Next ocell
 
Err_Hdlr1:
    If Err <> 0 Then
        If Err.Description = "No cells were found." Then
            MsgBox "No cells with text found in range AW8:AX" & lLastRow, vbOKInformation, "Note"
        End If
        Err.Clear
    End If
 
'+++++++++++++++check second range
 
    On Error GoTo Err_Hdlr2
    Set oNOCells = Sheets("SG_Tracker").Range("BC8:BD" & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
 
    For Each ocell In oNOCells
        MsgBox "There is text in cell " & ocell.Address & vbCrLf & _
        "make a note of it and correct it.", vbOKOnly, "Error!!!"
    Next ocell
 
Err_Hdlr2:
    If Err <> 0 Then
        If Err.Description = "No cells were found." Then
            MsgBox "No cells with text found in range BC8:BD" & lLastRow, vbOKInformation, "Note"
        End If
        Err.Clear
    End If
 
End Sub

Any help (including alternate methods to achieve the same thing) would be most appreciated.

 

[Rasm]

Try something along these lines - your code finishes - then goes to the error handler code - you dont want that.

Sub FindCellsWithText()
'adapted from [URL]http://vbadud.blogspot.com/2008_06_01_archive.html[/URL]
Dim lLastRow As Long
Dim oNOCells
'Find last row
    lLastRow = Range("W" & Rows.Count).End(xlUp).Row
 
'+++++++++++++++check first range
    'On Error GoTo Err_Hdlr1
    Set oNOCells = Sheets("SG_Tracker").Range("AW8:AX" & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
 
    For Each ocell In oNOCells
        If Err.Number <> 0 Then
            If Err.Description = "No cells were found." Then
                MsgBox "No cells with text found in range AW8:AX" & lLastRow, vbOKInformation, "Note"
            End If
            Err.Clear
        End If
        
    Next ocell
    '+++++++++++++++check second range
 
    
    Set oNOCells = Sheets("SG_Tracker").Range("BC8:BD" & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
 
    For Each ocell In oNOCells
        If Err.Number <> 0 Then
            MsgBox "what do I do"
            Err.Clear
        End If
        MsgBox "There is text in cell " & ocell.Address & vbCrLf & _
        "make a note of it and correct it.", vbOKOnly, "Error!!!"
    Next ocell
 

 
End Sub

 

[karabiner8]

Rasm, thanks for your replay. It looks like you removed the Error Handling altogether. When I tested your code I got the standard error message "Run-time error '1004': No cells were found." instead of the custom message that I want displayed. This was tested with not text in the columns to be checked.

Since I had to put a release of the spreadsheet out I just created 3 macros, one for each of the three ranges I needed to test and that works fine but I would think there is a more eloquent way to achieve this, especially when there are many non-contiguous ranges to be tested.

 

[karabiner8]

Well I finally sorted the whole thing out myself. The error handler wasn't working because I forgot to include a Resume Next statement. Once I had that figured out I used a For loop and an array variable to hold the different ranges I wanted to test. Here's the code; I hope it helps someone in the future.

Sub FindCellsWithText4Loop()
'based on http://vbadud.blogspot.com/2008_06_01_archive.html
 
Dim lLastRow As Long
Dim oNOCells
Dim sRng(1 To 3) As String 'holds non-contiguous ranges to test
Dim iCount As Integer 'counter for For loop
 
sRng(1) = "AW8:AX"
sRng(2) = "BC8:BD"
sRng(3) = "BI8:BJ"
 
MsgBox "This macro searches for values stored as text in the" & vbCrLf & _
    "mandatory Volume and Sales columns for YoL." & vbCrLf & _
    "Text in these columns will cause errors in the spreadsheet." & vbCrLf & _
    "" & vbCrLf & _
    "Make note of any cells reported, go to that cell and delete" & vbCrLf & _
    "it, then re-enter the value and hit <ENTER>.", vbInformation, "Note"
 
'Find last row
    lLastRow = Range("W" & Rows.Count).End(xlUp).Row
 
iCount = 1
    For iCount = 1 To 3
        On Error GoTo Err_Hdlr1
        Set oNOCells = Sheets("SG_Tracker").Range(sRng(iCount) & lLastRow).Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
 
        For Each ocell In oNOCells
            MsgBox "There is text in cell " & ocell.Address & vbCrLf & _
            "make a note of it and correct it.", vbOKOnly, "Error!!!"
        Next ocell
 
Err_Hdlr1:
        If Err <> 0 Then
            If Err.Description = "No cells were found." Then
                MsgBox "No cells with text found in range " & sRng(iCount) &  lLastRow, vbOKInformation, "Note"
            End If
            Debug.Print Err.Description
            Err.Clear
            Resume Next
        End If
 
    Next iCount
 
    Sheets("SG_Tracker").Range("AW8").Select
 
End Sub