Excel中的类型错配错误

Question

我在VBA中使用VLookUp功能。并在从VLookup返回记录时获取错误。excel中的值是字母数字。

下面是我的代码--

Sub SKUMISMATCH()

    Dim Wms_Row As Variant
    Dim Wms_Col As Variant

    Table1 = Sheet1.Range("A2:A243293")
    Table2 = Sheet1.Range("J2:K295445")

    Wms_Row = Sheet1.Range("G2").Row
    Wms_Col = Sheet1.Range("G2").Column

    For Each c1 In Table1
        Sheet1.Cells(Wms_Row, Wms_Col) = Application.WorksheetFunction.VLookup(c1, Table2, 2, False)
        Wms_Row = Wms_Row + 1
    Next c1

    MsgBox "VLookup Complete"

End Sub

​

​

Answer 1

试试看以下几点。您可能需要研究进一步优化的方法，以使代码运行得更快。

Option Explicit   'Always use

Public Sub SKUMISMATCH()

    Application.ScreenUpdating = False   'optimise code
    Application.Calculation = xlCalculationManual

    Dim Wms_Row As Long 'Declare with expected type not variant
    Dim ws As Worksheet
    Const Wms_Col As Long = 7 'declare as constant as doesn't change value

    Wms_Row = 2
    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Dim Table1 As Range 'Declare all variables
    Dim Table2 As Range
    Dim c1 As Range

    With ws 'use With statement to speed up code

        Set Table1 = .Range("A2:A243293") 'set range variables
        Set Table2 = .Range("J2:K295445")
        Table1.Offset(, 6).ClearContents   'Clear lookup return area in case changes to lookup range alters where errors may occur.

        For Each c1 In Table1

            On Error Resume Next 'skip non matches
               .Cells(Wms_Row, Wms_Col) = Application.WorksheetFunction.VLookup(c1, Table2, 2, False)
            On Error GoTo 0

            Wms_Row = Wms_Row + 1

        Next c1

    End With

    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

    MsgBox "VLookup Complete"

End Sub