我想请你帮忙处理我的代码。我正在尝试从另一个工作簿中获取值，并通过 VLOOKUP 将其放入当前工作簿中。我想使用对工作簿的引用而不是确切名称，因为搜索工作簿的名称会发生变化。这是代码：
Sub Thu_submission_ships()
FileToOpen = Application.GetOpenFilename("Microsoft Excel Files (*.xlsx), *.xlsx", 2, "Open last week's EMEA GA Backlog YYYY-MM-DD submission")
Workbooks.Open Filename:=FileToOpen
Set wb_blog = ActiveWorkbook
Set sht_blog = wb_blog.Sheets("Backlog")
Columns("X:X").NumberFormat = "@"
ActiveSheet.Range("X2:Z" & LastRow_blog).Name = "blog"
wb_blog.Save
FileToOpen = Application.GetOpenFilename("Microsoft Excel Files (*.xlsx), *.xlsx", 1, "Open today's GA ships")
Workbooks.Open Filename:=FileToOpen
Sheets("Ships").Select
LastRow2 = Cells(Rows.Count, 1).End(xlUp).Row
Columns("X:X").Insert Shift:=xlToRight
Range("X2:X" & LastRow2).Formula = "=F2&L2"
Range("X2:X" & LastRow2).Copy: Range("X2:X" & LastRow2).PasteSpecial xlPasteValues
Range("X2").Select
Columns("X:X").NumberFormat = "@"
Range("Y2:Y" & LastRow2).Formula = "=VLOOKUP(X2, sht_blog!blog, 2, False)"
End Sub
非常感谢您的帮助！
您可以在字符串变量中构建公式：
sFormula = "=VLOOKUP(X2,'[" & sht_blog.parent.name &"]" & sht_blog.name &"'!blog, 2,0)"
然后简单地：
Range("Y2").Formula = sFormula
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句