问题描述
我在工作簿文件工作。然后,我打开另一个用于查找数据的工作簿(Masterfile)。 Workbooks.Open FileName:= Path& Masterfile
lRowMasterfile = Cells(Rows.Count,A)。End(xlUp).Row
SelectionMasterfile = Range(A1)。CurrentRegion.Address
工作簿(文件)。激活
范围(K2)。FormulaR1C1 == VLOOKUP(RC [-1],'& Masterfile'&!& SelectionMasterfile,1 ,FALSE)
范围(K2)。自动填充目标:=范围(K2:K& lRowFile)
工作簿(Masterfile)。关闭
我定义Masterfile和Range在其他文档中使用它,但不幸的是它不起作用。有人可以帮助吗?
你有两个问题。
这一行给出了一个A1符号的地址(例如$ A $ 1:$ B $ 3):
SelectionMasterfile = Range A1)。CurrentRegion.Address
但是您正在使用R1C1符号构建公式(例如R1C1:R3C2 ),并且您缺少工作表名称。尝试这样:
SelectionMasterfile = ActiveSheet.Name& ! &安培;范围(A1)。CurrentRegion.Address(ReferenceStyle:= xlR1C1)
另一个问题是你有语音标记的错误
Range(K2)。FormulaR1C1 == VLOOKUP(RC [ -1],'[& Masterfile&]'& SelectionMasterfile&,1,FALSE)
PS您应该始终尝试完全限定床单和范围。查找使用工作簿
,工作表
和范围
对象的指南变量。
I'm working in the workbook "File". Then, I open another workbook (Masterfile) which is used to look up data.
Workbooks.Open FileName:=Path & Masterfile
lRowMasterfile = Cells(Rows.Count, "A").End(xlUp).Row
SelectionMasterfile = Range("A1").CurrentRegion.Address
Workbooks(File).Activate
Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & Masterfile"' & ! & SelectionMasterfile,1, FALSE)"
Range("K2").AutoFill Destination:=Range("K2:K" & lRowFile)
Workbooks(Masterfile).Close
I define Masterfile and Range to use it in other documents but unfortunately it does not work. Can anybody help?
You've got two issues.
This line gives you an address in A1 notation (e.g. $A$1:$B$3):
SelectionMasterfile = Range("A1").CurrentRegion.Address
But you are building the formula using R1C1 notation (e.g. R1C1:R3C2) and you are missing the worksheet name. Try this:
SelectionMasterfile = ActiveSheet.Name & "!" & Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)
The other problem is there's an error in where you've got speech marks.
Range("K2").FormulaR1C1 = "=VLOOKUP(RC[-1],'[" & Masterfile & "]'" & SelectionMasterfile & ",1, FALSE)"
PS You should always try to fully qualify sheets and ranges. Look up guides to using Workbook
, Worksheet
and Range
object variables.
这篇关于VBA vlookup具有定义的范围和来自其他工作簿的文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!