需要您在我的宏
上提供帮助我尝试从2个差异工作簿中vlookup。我的1个工作簿将根据日期每天更改名称。我已经明白了。现在,我坚持如何将可变的工作簿变暗以在Vlookup公式中使用。这是我的代码我想昏暗的OCBReport。
Sub Part_ETA_PLANNER()
'
'Part ETA PLANNER Macro
'
'
'Find OCB PLanner Today
Dim OCBDaily As Workbook
Dim t As Workbook
For Each t In Workbooks
If Left(t.Name, 11) = "OCB_Report_" Then
Set OCBDaily = Workbooks(t.Name)
End If
Next t
'Variable Dim
Dim PartNumber, myRange As Long
Dim OCBReport As Sheets
Set OCBReport = "[ & OCBDaily & ]OCB" ' I got error on this part'
PartNumber = Range("L2").Offset(0, -10).Address(0, 0)
myRange = "'" & OCBReport & "'!C:W"
'Vlookup Part ETA planner
Dim LastRow As Long
LastRow = Sheets("Unfulfilled Daily Report").Range("E" & Rows.Count).End(xlUp).Row
Sheets("Unfulfilled Daily Report").Range("L2").Formula = "=VLOOKUP(" & PartNumber & "," & myRange & ", 21, FALSE)"
Sheets("Unfulfilled Daily Report").Range("L2").AutoFill Destination:=Range("L2:L" & LastRow)
Sheets("Unfulfilled Daily Report").Range("L2:L" & LastRow).Copy
Sheets("Unfulfilled Daily Report").Range("L2:L" & LastRow).PasteSpecial xlPasteValues
Range("B2").Select
End Sub
您需要的是String
变量。另外,如果您在引号中放置一个变量,那么它将像字符串一样行为。OCBDaily.Name
也将为您提供可以在"[]"
更改
Dim OCBReport As Sheets
Set OCBReport = "[ & OCBDaily & ]OCB" ' I got error on this part'
to
Dim OCBReport As String
OCBReport = "[" & OCBDaily.Name & "]OCB"
如果我正确理解您的代码部分,则需要将其更改为:
Set OCBReport = OCBDaily.worksheets("OCB")
您的尝试试图将表变量设置为字符串,这给出了类型错误。'OCB'应该是通缉工作表的名称。
问候,Krossi