VLOOKUP在表中存在对应值时仍返回空值的问题排查与解决

问题现象 #

• 声明Variant类型变量执行VLOOKUP操作时，resultC返回空值，resultA和resultB返回错误2042；注释resultC相关代码后，程序可正常运行，A、B列能获取正确值
• 当Column C为会计格式数值（如$ 1000.89）时触发问题，改为普通数字（如30、40）则恢复正常

数据示例 #

关键代码片段 #

变量声明：

Dim resultA As Variant
Dim resultB As Variant
Dim resultC As Variant

VLOOKUP执行代码：

resultA = Application.VLookup(id, dataRange, resultColumnA.Column - dataRange.Columns(1).Column + 1, False)
resultB = Application.VLookup(id, dataRange, resultColumnB.Column - dataRange.Columns(1).Column + 1, False)
resultC = Application.VLookup(id, dataRange, resultColumnC.Column - dataRange.Columns(1).Column + 1, False)

完整复现代码 #

Sub AbraKadabra()
    Dim wb As Workbook
    Dim mainSheet As Worksheet
    Dim dataSheet As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim mainColA As Range
    Dim mainColB As Range
    Dim mainColC As Range
    Dim dataLastRow As Long
    Dim dataRange As Range
    Dim lookupRange As Range
    Dim idColumn As Range
    Dim resultColumnA As Range
    Dim resultColumnB As Range
    Dim resultColumnC As Range
    Dim colNum1 As Integer
    Dim colNum2 As Integer
    Dim colNum3 As Integer
    Dim testVar As Variant
    
    ' Set the workbook and main sheet
    Set wb = ThisWorkbook
    Set mainSheet = wb.Worksheets("mainSheet") ' Use main sheet name
    
    ' Find the last row in the main sheet
    lastRow = mainSheet.Cells(mainSheet.Rows.Count, "A").End(xlUp).Row
    colNum1 = Application.WorksheetFunction.Match("Column A", mainSheet.Rows(1), 0)
    colNum2 = Application.WorksheetFunction.Match("Column B", mainSheet.Rows(1), 0)
    colNum3 = Application.WorksheetFunction.Match("Column C", mainSheet.Rows(1), 0)
    
    Set mainColA = mainSheet.Columns(colNum1)
    Set mainColB = mainSheet.Columns(colNum2)
    Set mainColC = mainSheet.Columns(colNum3)

    ' Set the ranges for the main sheet columns
    ' Set mainColA = mainSheet.Rows(1).Find("Column A", LookIn:=xlValues, LookAt:=xlWhole)
    ' Set mainColB = mainSheet.Rows(1).Find("Column B", LookIn:=xlValues, LookAt:=xlWhole)
    ' Set mainColC = mainSheet.Rows(1).Find("Column C", LookIn:=xlValues, LookAt:=xlWhole)
    
    If mainColA Is Nothing Or mainColB Is Nothing Or mainColC Is Nothing Then
        MsgBox "One or more column headers not found in the main sheet.", vbExclamation
        Exit Sub
    End If

      ' Loop through each row in the main sheet
    For i = 2 To lastRow ' Assuming the data starts from row 2, change as needed
        ' Get the ID for each row in the main sheet
        Dim id As Variant
        id = mainSheet.Cells(i, 1).Value ' Assuming the ID is in column A, change as needed
        
        ' Loop through each data sheet
        For Each dataSheet In wb.Sheets
            If dataSheet.Name <> mainSheet.Name Then ' Skip the main sheet itself
                ' Find the last row in the data sheet
                dataLastRow = dataSheet.Cells(dataSheet.Rows.Count, "A").End(xlUp).Row
                
                ' Set the range for the data sheet columns
                Set dataRange = dataSheet.Range("A1:Z" & dataLastRow) ' Adjust the range as needed
                
                ' Set the range for the ID column and the result columns in the data sheet
                Set idColumn = dataRange.Columns(1) ' Assuming the ID column is in column A
                Set resultColumnA = dataRange.Rows(1).Find("Column A", LookIn:=xlValues, LookAt:=xlWhole)
                Set resultColumnB = dataRange.Rows(1).Find("Column B", LookIn:=xlValues, LookAt:=xlWhole)
                Set resultColumnC = dataRange.Rows(1).Find("Column C", LookIn:=xlValues, LookAt:=xlWhole)
                
                ' Set the lookup range for VLOOKUP
                ' Set lookupRange = dataRange.Columns(1) ' Assuming the lookup range is the ID column

                ' Use VLOOKUP to find the corresponding values in the data sheet
                Dim resultA As Variant
                Dim resultB As Variant
                Dim resultC As Variant
                resultA = Application.VLookup(id, dataRange, resultColumnA.Column - dataRange.Columns(1).Column + 1, False)
                resultB = Application.VLookup(id, dataRange, resultColumnB.Column - dataRange.Columns(1).Column + 1, False)
                resultC = Application.VLookup(id, dataRange, resultColumnC.Column - dataRange.Columns(1).Column + 1, False)

                
                If Not IsError(resultA) Then
                    ' Populate particular columns in the main sheet with data from the data sheet
                    mainSheet.Cells(i, mainColA.Column).Value = resultA
                    mainSheet.Cells(i, mainColB.Column).Value = resultB
                    mainSheet.Cells(i, mainColC.Column).Value = resultC
                   
                    Exit For ' Exit the loop if a match is found in the current data sheet
                End If
            End If
        Next dataSheet
    Next i
    
    ' Cleanup
    Set mainSheet = Nothing
    Set wb = Nothing
End Sub

问题根源与解决方案 #

• 代码本身无错误，问题出在Column C的表头格式：表头被设置为会计格式，而非常规（General）格式
• 解决方法：确保表格的列头使用常规格式

内容的提问来源于stack exchange，提问作者Dehshak