我使用以下代码对表执行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”类型
1条答案
按热度按时间8e2ybdfx1#
我想你会发现这样的东西更容易维护。当对同一范围执行多个查找时,执行单个
Match()
以获取匹配的行,然后从该行获取值会更快。这还允许您在单个位置更改标题的数量或它们的值。