使用VBA的Excel:参考工作表代码名称而不是选项卡名称

Question

因为我每周都会更新选项卡名称，而不是选项卡名称，所以我想使用 VBA 项目工作表名称 (Sheet17(，如果我更改选项卡名称，它不会改变。

我的代码是：

With ThisWorkbook.Sheets("update raw data")
Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
Dim i As Integer
For i = 4 To LastRow
Cells(i, 56).Value = WorksheetFunction.Index(Sheet17.Range("O3:O300"),WorksheetFunction.Match(Cells(i,69).Value, Sheet17.Range("AB3:AB300"), 0))
Next i
End With

但它给了我这个错误：

'1004' -Unable to get the Match property of the WorksheetFunction class

Answer 1

试试这个。

Sub test()
Dim LastRow As Long, i As Integer
With ThisWorkbook.Sheets("update raw data")
LastRow = .Cells(.Rows.Count, 4).End(xlUp).Row
For i = 4 To LastRow
.Cells(i, 56) = WorksheetFunction.Index(Sheet17.[O3:O300], WorksheetFunction.Match(.Cells(i, 69), Sheet17.[AB3:AB300], 0))
Next i
End With
End Sub