Excel VBA: If value not found in range, go to

3
votes

I am using the following code to loop through sheets 1-31 filtering each sheet by the value in a cell("E1") in sheets("RunFilter_2") and then copy the filtered range and copy to the next empty row in sheets("RunFilter_2").

The code errors when it doesn't find the value of sheets("RunFilter_2").Range("E1") in column 18 of the active sheet.

So I added a range check, that checks if sheets("RunFilter_2").Range("E1").Value is found in column Range("R:R").

But, how do I move to the Next I If rngFound Is Nothing?

Sub RunFilter2()

Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
Range("A1").Select

    Dim wb As Workbook
    Dim ws As Worksheet

    Set wb = ActiveWorkbook
    Set ws = wb.sheets("01")

         Dim WS_Count As Integer
         Dim I As Integer

         WS_Count = ActiveWorkbook.Worksheets.Count - 3

         For I = 1 To WS_Count

Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count

sheets(I).Select

Columns("A:U").Select

Dim rng As Range
Dim rngFound As Range

Set rng = Range("R:R")

Set rngFound = rng.Find(sheets("RunFilter_2").Range("E1").Value)

If rngFound Is Nothing Then

'----------------------------------
' How do I code ... GO TO Next I
'----------------------------------

Else:

Selection.AutoFilter

ActiveSheet.Range("$A$1:$U" & LastRow).AutoFilter Field:=18, Criteria1:=sheets("RunFilter_2").Range("E1").Value

    Range("A1").Offset(1, 0).Select
    Rows(ActiveCell.Row).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy

sheets("RunFilter_2").Select

If Range("A4").Value = "" Then

Range("A4").Select

Else

Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select

End If

ActiveSheet.Paste
ws.Select
Application.CutCopyMode = False
Selection.AutoFilter
Range("A1").Select
sheets("RunFilter_2").Select

         Next I

End Sub
4
vbaexcel

4 Answers

3
votes

Place some label before Next I:

NextI:
    Next I

Then you can do this:

If rngFound Is Nothing Then
    Goto NextI
Else
    ....

Alternatively you can simplify it without needing the else statement

If rngFound Is Nothing Then Goto NextI
.... ' Proceed without the need for `Else` and `End If`

EDIT.. Some more

While it is generally considered bad programming practice to use Goto statements, it is not the case in this specific situation. It is just used as a workaround for the lack of the continue statement that exists in the C and derived languages.

5
votes

You can do it like this:

For I = 1 To WS_Count
    If rngFound Is Nothing Then goto NextIPlace
    your code
NextIPlace:
Next I

But you should reconsider writing like this, it is not a good VBA practice to use GoTo. The whole code should be changed. Check more here. Once your code works, feel free to submit it at https://codereview.stackexchange.com/, they would give you good ideas.

4
votes

There is no need to use GoTo here. The simple way to accomplish this is with the following: 

For I = 1 To WS_Count

    ' do stuff 

    If Not rngFound is Nothing

       'execute desired action

    End If

    ' do more stuff

Next i

You can also place the do more stuff inside the first if block if needed. The code in your post was kind of messy and I didn't take time to dissect fully.

2
votes

you should add a marker before Next I

MARKER:
Next I

So after If rngFound Is Nothing Then you add GoTo MARKER