Resuming VBA if cell value not found.

[Darranimo]

I am trying to scan a range for different specific numbers. For every instance where the number is found an action is then performed. However, if one of the specific numbers is not located in the range I get errors. How do I modify my code so that if "9111" isn't found it will start looking for "2990"? There are about 10 different numbers I am searching for.

Dim SelectCells As Range
Dim xCell As Object
Dim Rng As Range
Dim Ws1 As Worksheet

Set Ws1 = Worksheets("WF")
Set Rng = Ws1.Range("F1:F500")
Set SelectCells = Nothing

For Each xCell In Rng
If xCell.Value = "9111" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next

***Do something***

For Each xCell In Rng
If xCell.Value = "2990" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next

***Do something***

 

[ranman256]

you didn't show the line that fails. (debug)

 

[Joe4]

Welcome to the Board!

See if something like this does what you want (this will select all the cells in column F that match one of your values):

Sub MyFindValues()

    Dim SelectCells As Range
    Dim xCell As Object
    Dim Rng As Range
    Dim Ws1 As Worksheet
    Dim lr As Long
    Dim arr() As Variant
    Dim i As Long
    

    Set Ws1 = Worksheets("WF")
    
'   Set array of values to look for
    arr = Array("9111", "2990", "1234")
    
'   Find last row with data in column F on worksheet
    lr = Ws1.Cells(Rows.Count, "F").End(xlUp).Row
    
    Set Rng = Ws1.Range("F1:F" & lr)
    Set SelectCells = Nothing

'   Loop through all cells in range
    For Each xCell In Rng
'       Loop through each value in array
        For i = LBound(arr) To UBound(arr)
'           Check to see if value is found in cell
            If xCell.Value = arr(i) Then
                If SelectCells Is Nothing Then
                    Set SelectCells = xCell
                Else
                    Set SelectCells = Union(SelectCells, xCell)
                End If
'               Exit loop, as value already found (no need to check others)
                Exit For
            End If
        Next i
    Next xCell

'   Select range
    If SelectCells Is Nothing Then
        MsgBox "Found no matches"
    Else
        SelectCells.Select
    End If

End Sub

Just add all the values that you want to look for to the array, i.e.

    arr = Array("9111", "2990", "1234")

Also note that if all the value you want to look for are actually entered as numbers and not text, remove the parentheses from around them in the array, i.e.

 arr = Array(9111, 2990, 1234)

 

[Darranimo]

My apologies. Here is some more context.

Dim SelectCells As Range
Dim xCell As Object
Dim Rng As Range
Dim Ws1 As Worksheet
Dim lrw6 As Long
Dim lrw7 As Long

Set Ws1 = Worksheets("WF")
Set Rng = Ws1.Range("F1:F500")
Set SelectCells = Nothing

lrw6 = Sheets("SCV Land (9111)").Cells(Rows.Count, "S").End(xlUp).Row
lrw7 = Sheets("SCVRP Hsng (9129)").Cells(Rows.Count, "S").End(xlUp).Row

'Search for first number
For Each xCell In Rng
If xCell.Value = "9111" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next

'Copy and paste to different worksheet
SelectCells.Select (DEBUG)
Selection.Offset(0, -5).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count + 18).Select
Ws1.Names.Add Name:="SCVLand", RefersTo:=Selection
Range("SCVLand").Copy Worksheets("SCV Land (9111)").Range("A" & lrw6 + 1)
Set SelectCells = Nothing

'Begin searching for next number.
For Each xCell In Rng
If xCell.Value = "9129" Then
If SelectCells Is Nothing Then
Set SelectCells = Range(xCell.Address)
Else
Set SelectCells = Union(SelectCells, Range(xCell.Address))
End If
End If
Next

SelectCells.Select
Selection.Offset(0, -5).Select
Selection.Resize(Selection.Rows.Count + 0, Selection.Columns.Count + 18).Select
Ws1.Names.Add Name:="SCVHsng", RefersTo:=Selection
Range("SCVHsng").Copy Worksheets("SCVRP Hsng (9129)").Range("A" & lrw7 + 1)
Set SelectCells = Nothing

 

[Darranimo]

Welcome to the Board!

See if something like this does what you want (this will select all the cells in column F that match one of your values):

Sub MyFindValues()

    Dim SelectCells As Range
    Dim xCell As Object
    Dim Rng As Range
    Dim Ws1 As Worksheet
    Dim lr As Long
    Dim arr() As Variant
    Dim i As Long
   

    Set Ws1 = Worksheets("WF")
   
'   Set array of values to look for
    arr = Array("9111", "2990", "1234")
   
'   Find last row with data in column F on worksheet
    lr = Ws1.Cells(Rows.Count, "F").End(xlUp).Row
   
    Set Rng = Ws1.Range("F1:F" & lr)
    Set SelectCells = Nothing

'   Loop through all cells in range
    For Each xCell In Rng
'       Loop through each value in array
        For i = LBound(arr) To UBound(arr)
'           Check to see if value is found in cell
            If xCell.Value = arr(i) Then
                If SelectCells Is Nothing Then
                    Set SelectCells = xCell
                Else
                    Set SelectCells = Union(SelectCells, xCell)
                End If
'               Exit loop, as value already found (no need to check others)
                Exit For
            End If
        Next i
    Next xCell

'   Select range
    If SelectCells Is Nothing Then
        MsgBox "Found no matches"
    Else
        SelectCells.Select
    End If

End Sub

Just add all the values that you want to look for to the array, i.e.

    arr = Array("9111", "2990", "1234")

Also note that if all the value you want to look for are actually entered as numbers and not text, remove the parentheses from around them in the array, i.e.

 arr = Array(9111, 2990, 1234)

THIS IS SO HELPFUL! I clearly need to learn to use arrays. Do you mind looking at the extra code I posted? When a number is found and the range selected I would like to copy and paste to another worksheet. Where would that code fit in here?

To add more color, the data I am working with is an amalgamation of bank activity of 11 different bank accounts. This macro is to take all activity with bank account ending "9111" and paste it to it's designated worksheet. Then the same for each following bank account. The problem I ran into was when my code searched for a bank account number but there was no activity so it didn't find the number. I hope I am explaining this well enough. Any help is appreciated!

 

[Darranimo]

THIS IS SO HELPFUL! I clearly need to learn to use arrays. Do you mind looking at the extra code I posted? When a number is found and the range selected I would like to copy and paste to another worksheet. Where would that code fit in here?

To add more color, the data I am working with is an amalgamation of bank activity of 11 different bank accounts. This macro is to take all activity with bank account ending "9111" and paste it to it's designated worksheet. Then the same for each following bank account. The problem I ran into was when my code searched for a bank account number but there was no activity so it didn't find the number. I hope I am explaining this well enough. Any help is appreciated!

The (DEBUG) actually occurs on the second instance of SelectCells.Select

 

[Joe4]

What is the error message you get?
Which line of code exactly is highlighted when you hit bebug?

Try checking the range first before selecting it, like I showed you in the code I posted:

'   Select range
    If SelectCells Is Nothing Then
        MsgBox "Found no matches"
        Exit Sub
    Else
        SelectCells.Select
    End If

as you will get an error if you try to select an empty range.