excel - 即使使用 IsError,Excel VBA Vlookup 也会返回错误 2042
问题描述
以下代码不起作用。我的 VLOOKUP 函数出现 2042 错误,但是无论我做什么我都无法解决它。我一直在使用 if ISERROR ,但它仍然无法正确捕获它,从而损害了我的整个宏。如果我运行本地窗口,您可以看到要搜索的值存储在数组“arr”中,如果在“目标”范围内未找到,即使后续条目也返回 2042。
Sub test()
ThisWorkbook.Activate
Worksheets.add
Worksheets("Test4").Range("A1:T110").copy Destination:=ActiveSheet.Range("A1")
With ActiveSheet
Dim Search_Array As Variant
Search_Array = Range("C2", Range("C1").End(xlDown)) 'use this array to loop through the value to search for
Dim Target_MatchValue As Integer
Dim Target_Range As Range
Dim arr As Variant
Dim counter As Integer
Dim n As Integer
counter = 0
n = 0
Target_MatchValue = 0
For counter = LBound(Search_Array) To UBound(Search_Array)
Target_MatchValue = 0
Target_MatchValue = Application.Match(Search_Array(counter, 1), .Range("H2:H200"), 0) - 1
Set Target_Range = .Range(.Cells(2 + n, 8), .Cells(1000, 9))
arr = Application.VLookup(Search_Array(counter, 1), Target_Range, 2, False)
If IsError(arr) Then
.Range(Cells(1 + counter, 6), Cells(1 + counter, 6)).value = "N/A"
Else
.Range(Cells(1 + counter, 6), Cells(1 + counter, 6)).value = arr 'Return the value of the array in this cell
End If
Target_Range.Select
If Target_MatchValue = 0 Then
n = n + 1
ElseIf Target_MatchValue > 0 Then
n = n + Target_MatchValue
End If
Next counter
End With
End Sub
解决方案
Sub test()
Dim Search_Array As Variant
Dim Target_MatchValue As Variant
Dim Target_Range As Range
Dim arr As Variant
Dim counter As Integer
Dim n As Integer
Worksheets("Test4").Range("A1:T110").copy Destination:=ActiveSheet.Range("A1")
With ActiveSheet
'data must be ordered in order to apply the non-repetitive condition
Search_Array = Sheet1.Range("A2", Sheet1.Range("A1").End(xlDown)) 'use this array to loop through the value to search for
n = 0
With ActiveSheet
For counter = LBound(Search_Array) To UBound(Search_Array)
Target_MatchValue = 0
Target_MatchValue = Application.Match(Search_Array(counter, 1), .Range(Cells(2 + n, 4), Cells(1000, 4)), 0) 'The problem was here. "A1:T110" did not allowed to the shifting range to change. Now this code will return the value used for the shifting range
Set Target_Range = .Range(Cells(2 + n, 4), Cells(1000, 5)) 'this is supposed to work as a shifting range allowing to match entries without making repetitions. I used the MATCH function in order to set the start of the range. i.e. if there is a match in the target table the range will shift from the location of the match downwards. If the match is at on the same level then it does not shift the range in order to match the same-level entry afterwards it is supposed to shift by one unit in order to prevent repetitions.
'target_range.select Activate this code in order to see the macro in action
arr = Application.VLookup(Search_Array(counter, 1), Target_Range, 2, False) 'store the vlookup value in an array in order to increase the efficiency the code and to speed up the whole proces
If IsError(arr) Then
.Cells(2 + n, 2).value = "" 'if the macro does not find anything, no value will be recorded anywhere
Else
.Cells(1 + n + Target_MatchValue, 2).value = Search_Array(counter, 2) 'Return the value of the search_array in this cell so to match column A values with column D values if they are found
End If
If IsError(arr) Then
n = n
ElseIf Target_MatchValue = 0 Then 'if the macro does not find anything, the shifting range does not shift so that subsequent values can be searched in the same range without missing precious matches
n = n + 1
ElseIf Target_MatchValue > 0 Then 'if there is a matching value between Column A and Column B, the shifting range shifts by the n + the distance between the the current vlookupvalue and the found value. Note that Data must be stored in a filtered order otherwise vlookup will not work correctly
n = n + Target_MatchValue
End If
Next counter
End With
End Sub
解决方案
声明你的Target_MatchValue As Variant
所以不会引发错误,而是你必须处理你想要做什么IsError(Target_MatchValue)
(当没有找到匹配项时)
推荐阅读
- java - 如何在 Firebase 中提供默认数据
- javascript - 在反应中将对象绑定为组件状态键的值的正确方法
- go - 展平结构并将其转换为 map[string]string
- java - 有没有办法使用 Maven 和 Junit 5 暂存数据?
- image - 如何使用 1 通道图像作为 VGG 模型的输入
- javascript - 在 Photoshop 中打开的多个图像根据文件名保存为不同的文件大小
- jsf - Primefaces中的对话框内可能有复合组件吗?
- linq - EF Core 2.1 更新导致在相关表中创建记录
- xslt - XSL 1.0 对 SalesAmount 和相关/加入的税务记录进行分组和求和
- json - 调用 CreateRole 操作时格式错误的策略文档