我试图创建一个宏,如果在主数据表中找到产品,则返回“有效”,如果没有找到,则返回“无效”。我正在使用工作表函数vlookup来完成此操作。
下面是代码:
Sub fg_type()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Dim wb As Workbook
Dim ws As Worksheet
Dim ref_wb As Workbook
Dim ref_ws As Worksheet
Dim lastRow As Long
Dim ref_lastRow As Long
Dim lookup_val As Variant
Dim table_arr As Variant
Dim fgtype As Variant
Set wb = Workbooks("un_orders.xlsm")
Set ws = wb.Worksheets("uo")
Set ref_wb = Workbooks("target.xlsx")
Set ref_ws = ref_wb.Worksheets("Document")
lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
ref_lastRow = ref_ws.Cells(ref_ws.Rows.Count, "G").End(xlUp).Row
table_arr = ref_ws.Range("G3:G" & ref_lastRow)
For i = 2 To lastRow
lookup_val = ws.Range("C" & i).Value
On Error Resume Next
Err.Clear
fgtype = Application.WorksheetFunction.VLookup(lookup_val, table_arr, 1, False)
If Err.Number = 0 Then
fgtype(i, 1) = "valid"
Else
fgtype(i, 1) = "invalid"
End If
ws.Range("A" & i).Value = fgtype(i, 1)
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
字符串
但是,在运行代码之后,所有返回都是空的。我做错了什么?
1条答案
按热度按时间juud5qan1#
试试这个:
字符串
在不必要的时候总是禁用自定义错误处理,因为它会隐藏错误消息,正如Notus_Panda评论的那样。