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

Question

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

Community · Accepted Answer · 2017-04-13 12:40:36Z

6

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.

edited Apr 13, 2017 at 12:40

CommunityBot

11 silver badge

answered Mar 1, 2017 at 13:49

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

A.S.H Over a year ago

Hi Vityata, I agree that in general the use of Goto statements is not a good programming practice, but not in this specific case. Other languages usually have statement to break or continue the loops (i.e. continue in the C family). VBA not having this option is sometimes tedious and using this workaround might make the code more readabale. Just my humble opinion :)

Vityata Over a year ago

@A.S.H, I use GoTo more often than I would ever agree. And yes, VBA needs a way to break loops.

A.S.H · Accepted Answer · 2017-03-01 14:02:28Z

4

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.

edited Mar 1, 2017 at 14:02

answered Mar 1, 2017 at 13:49

A.S.H

29.4k6 gold badges25 silver badges50 bronze badges

Comments

Scott Holtzman · Accepted Answer · 2017-03-01 13:51:25Z

4

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.

answered Mar 1, 2017 at 13:51

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

2 Comments

R3uK Over a year ago

I was about to talk about spaghetti programs with GoTo! I was about to do the same suggestion but I'd add a boolean to avoid doing more stuff regarding the structure of the OP! ;)

Chris Maurer Over a year ago

You can also code it as rngfound IsNot Nothing, which may or may not be a little clearer...

Diego Ribba · Accepted Answer · 2017-03-01 13:49:35Z

2

you should add a marker before Next I

MARKER:
Next I

So after If rngFound Is Nothing Then you add GoTo MARKER

answered Mar 1, 2017 at 13:49

Diego Ribba

1409 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related