我使用以下代码对表执行Vlookup
我将这3个变量声明为变量，这样就不会有任何类型不匹配的错误。

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)

错误出现在resultC中，调试器显示它为“空”，resultA和resultB为错误2042。
当我注解掉结果C时，代码按预期工作（结果A和结果B能够存储正确的值）
下面是我正在使用的数据示例
| 色谱柱A| B柱|色谱柱C| Column C |
| --|--|--| ------------ |
| 测试数据|示例文本|一千块八毛九| $ 1000.89 |
| 测试数据1|示例文本|一千块六毛七| $ 1000.67 |
我尝试将变量声明为变量类型，但没有成功。
当我将表中的值更改为较小的数字（30，40，50）时，代码正常工作，但不适用于这些会计类型的数字（$5000.89）
下面是完整的代码，您可以使用它来重新创建错误。

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

最终更新：很抱歉造成了混乱，代码运行得很好。问题是C列标题的类型是不同的类型（应该是“General”，但它是“Accounting”类型）。
TLDR;确保表的列标题为“General”类型