VBA .查找.测距误差方法

Question

我试图在VBA中使用hLookup来引用同一个工作簿中的另一个工作表。我显式地引用了所有的单元格，但是在hLookup中仍然有一个错误。

Sub updateSettles()

Dim settles As Worksheet
Dim swaps As Worksheet
Dim flys As Worksheet
Dim lookupRange As Range



Set settles = ThisWorkbook.Worksheets("Settles")
Set swaps = ThisWorkbook.Worksheets("Swaps")
Set flys = ThisWorkbook.Worksheets("Flys")

tradeDate = flys.Range("y2").Value

'update settles
lastcol = settles.Range("b3").End(xlToRight).Column
lastcol2 = swaps.Range("b1").End(xlToRight).Column
Set lookupRange = swaps.Range(swaps.Cells(2, 2), swaps.Cells(13, lastcol2))

For i = 2 To lastcol
settles.Range(4, i).Value =   Application.WorksheetFunction.HLookup(settles.Range(settles.Cells(2, i)),   swaps.Range(swaps.Cells(2, 2), swaps.Cells(13, lastcol2)), 12, False)
Next i


End Sub

Answer 1

HLookup(settles.Range(settles.Cells(2, i)),   swaps.Range(swaps.Cells(2, 2), swaps.Cells(13, lastcol2)), 12, False)

          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

Range方法需要一个字符串地址或两个单元作为参数。它不使用一个单元格作为参数。你的意思是：

HLookup(settles.Cells(2, i).Value, swaps.Range(swaps.Cells(2, 2), swaps.Cells(13, lastcol2)), 12, False)