Excel VBA：如果在范围内找不到值，请转到
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42533405/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA: If value not found in range, go to
提问by SMORF
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").
我正在使用以下代码循环遍历工作表 1-31，通过工作表（“RunFilter_2”）中的单元格（“E1”）中的值过滤每个工作表，然后复制过滤的范围并复制到工作表中的下一个空行（ “运行过滤器_2”）。
The code errors when it doesn't find the value of sheets("RunFilter_2").Range("E1") in column 18 of the active sheet.
当它在活动工作表的第 18 列中找不到 sheet("RunFilter_2").Range("E1") 的值时，代码会出错。
So I added a range check, that checks if sheets("RunFilter_2").Range("E1").Value is found in column Range("R:R").
所以我添加了一个范围检查，检查是否在列 Range("R:R") 中找到 sheet("RunFilter_2").Range("E1").Value。
But, how do I move to the Next I If rngFound Is Nothing?
但是，如果 rngFound 什么都没有，我如何移动到下一个 I？
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:$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
回答by Vityata
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.
但是你应该重新考虑这样写，使用GoTo. 应该更改整个代码。在此处查看更多信息。一旦您的代码有效，请随时在https://codereview.stackexchange.com/提交它，他们会给您好主意。
回答by A.S.H
Place some label before Next I:
在之前放置一些标签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
或者，您可以在不需要 else 语句的情况下简化它
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 Gotostatements, it is not the case in this specific situation. It is just used as a workaround for the lack of the continuestatement that exists in the C and derived languages.
虽然使用Goto语句通常被认为是不好的编程习惯，但在这种特定情况下并非如此。它只是用作缺少continueC 语言和派生语言中存在的语句的解决方法。
回答by Scott Holtzman
There is no need to use GoTohere. The simple way to accomplish this is with the following:
这里没有必要使用GoTo。完成此操作的简单方法如下：
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 stuffinside the first if block if needed. The code in your post was kind of messy and I didn't take time to dissect fully.
do more stuff如果需要，您还可以将第一个 if 块放在里面。您帖子中的代码有点乱，我没有花时间完全剖析。
回答by Diego Ribba
you should add a marker before Next I
你应该在之前添加一个标记 Next I
MARKER:
Next I
So after If rngFound Is Nothing Thenyou add GoTo MARKER
所以在If rngFound Is Nothing Then你添加之后GoTo MARKER
