本文介绍了VBA vlookup具有定义的范围和来自其他工作簿的文件的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在工作簿文件工作。然后,我打开另一个用于查找数据的工作簿(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具有定义的范围和来自其他工作簿的文件的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-31 08:57