我需要帮助在我的代码中添加"if range is nothing then"部分。
假设我有4张工作表：
-
项目1工作表-应跳过，筛选范围为零，项目编号不在原始文件中
-
项目2工作表-代码需要运行，fitlered范围不是零，项目编号存在
-
项目3工作表-应跳过，筛选范围为零，项目编号不在原始文件中
-
项目4工作表-代码需要运行，fitlered范围不是零，项目编号存在
项目3未被跳过，正在读取最后一个工作表(项目2(的非空区域。请告知。
代码：
' dim declarations...
' set assignments....
If Worksheets("Raw").AutoFilterMode = True Then
Worksheets("Raw").AutoFilterMode = False
End If
With ws_raw
int_last_row_of_raw = .Cells(.Rows.Count, 1).End(xlUp).Row
int_last_col_of_raw = 16
Set rng_raw = .Range("A2", .Cells(int_last_row_of_raw, int_last_col_of_raw))
End With
With ws_raw.Sort
.SortFields.Clear
.SortFields.Add2 Key:=Range("A2"), Order:=xlAscending
.SortFields.Add2 Key:=Range("L2"), Order:=xlDescending
.SetRange rng_raw
.Header = xlYes
.Apply
End With
For Each ws In ThisWorkbook.Worksheets
If Not ws Is ws_raw _
And Not ws Is ws_master_tracker _
And Not ws Is ws_title_page _
And Not ws Is ws_sample _
And Not ws Is ws_closing _
And Not ws Is ws_ref _
And Not ws Is ws_pdf_template _
And Not ws.Visible = xlSheetHidden Then
project_name = ws.Range("E3").Value
With rng_raw
.AutoFilter 1, project_name
End With
On Error Resume Next
Set rng_filtered_raw = ws_raw.Range("J3", ws_raw.Cells(int_last_row_of_raw, int_last_col_of_raw)).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
' not doing its thing on project 3 worksheet
If rng_filtered_raw Is Nothing Then
If project_name = "" Then
project_name = "[BLANK]"
End If
not_found_project_numbers = not_found_project_numbers & ws.Name & " : " & project_name & vbNewLine
MsgBox ("skipped, project number not found, moving to the next ws")
Else
int_last_row_of_ws = 46
For int_current_row_of_ws = 11 To int_last_row_of_ws
cell_value = ws.Cells(int_current_row_of_ws, 3).Value
Select Case cell_value
'Concept Stage
Case Is = "Creation,Overview,Concept,Scope,Potential"
module_to_look_for = "Project Creation"
Case Is = "Product Briefing !"
' 20 or 30 more cases
' Others that are manually typed
Case Else
module_to_look_for = "MANUAL"
End Select
If Not rng_filtered_raw Is Nothing Then
If module_to_look_for = "MANUAL" Then
' Do nothing
' Highlight cell, etc.
Else
look_up_result = Application.WorksheetFunction.VLookup(module_to_look_for, rng_filtered_raw, 3, False)
If look_up_result = "" Then
ws.Cells(int_current_row_of_ws, 56).Value = "Blank Date!"
Else
ws.Cells(int_current_row_of_ws, 56).Value = look_up_result
End If
End If
End If
Next int_current_row_of_ws
End If
End If
Next ws
只是扩展一下我上面的评论，它似乎已经完成了任务：
由于On Error Resume Next，每当无法设置范围对象时，范围对象都会被而不是设置为零，而是保持设置为在循环的上一次迭代中设置的值。(如果没有On Error Resume Next语句，每当无法设置rng_filtered_raw范围对象时，就会出现运行时错误(。由于这个原因，下面的条件语句在range对象为nothing时捕获，不能按预期工作。
因此，在On Error Resume Next语句之前添加Set rng_filtered_raw = Nothing应该可以解决这个问题。