VBA Excel vlookup in loop 问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7100927/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Excel vlookup in loop problem
提问by carol
I have a problem with my VBA code in an excel spreadsheet containing orders. Each row contains a customer number, which I use to look up the customer email address, contained in a different sheet in the workbook.
我在包含订单的 Excel 电子表格中的 VBA 代码有问题。每行包含一个客户编号，我用它来查找客户电子邮件地址，该地址包含在工作簿的不同工作表中。
The vlookup code works fine for a single cell, but the problem is when I try to loop through all of the rows of the spreadsheet. The Excel formula for a single cell is, e.g.,
vlookup 代码适用于单个单元格，但问题是当我尝试遍历电子表格的所有行时。单个单元格的 Excel 公式是，例如，
=VLOOKUP(B2,Customers!A2:D1000,4,FALSE)
The VBA code generated for this is:
为此生成的 VBA 代码是：
Range("M2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],Customers!RC[-12]:R[999]C[-9],4,FALSE)"
Incorporating this into a loop, after selecting the starting cell, I have the following:
将其合并到一个循环中，在选择起始单元格后，我有以下内容：
Cells(2, 13).Select
Do
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],Customers!RC[-12]:R[999]C[-9],4,FALSE)"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, -10))
The problem is that I want the "table array" to be fixed, not relative to the cell whose value is being looked up. But I absolutely can't figure out how to do it. If I change the code as follows, I get a run-time error:
问题是我希望“表数组”是固定的，而不是相对于正在查找其值的单元格。但我绝对不知道该怎么做。如果我按如下方式更改代码，则会出现运行时错误：
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],Customers!A2:D1000,4,FALSE)"
I have tried quoting, unquoting, setting a range variable, using the range variable with .address... can someone pleasehelp?
我曾尝试引用，unquoting，设置了一系列变量，使用带有。地址范围变量...有人可以请帮助？
Thank you so much.
非常感谢。
采纳答案by transistor1
I'm pretty sure the brackets in your R1C1 formula indicate that you are specifying a relative range (especially with the negatives in them). If you want to specify an absolute range, you need to specify the R1C1 cells withoutbrackets; e.g. R2C2:R4C4.
我很确定您的 R1C1 公式中的括号表明您正在指定一个相对范围（尤其是其中的负数）。如果要指定绝对范围，则需要指定不带括号的 R1C1 单元格；例如 R2C2:R4C4。
As a simple example:
作为一个简单的例子：
Sub test()
Sheet1.Range("C5").FormulaR1C1 = "=VLOOKUP(1,R1C1:R3C3,2,FALSE)"
End Sub
Running this gives you an absolute "A1"-style formula in cell C5.
运行它会在单元格 C5 中为您提供绝对的“A1”样式公式。
I think your problem might be:
我想你的问题可能是：
Customers!RC[-12]:R[999]C[-9]
Because it is a relative range. You have to explicitly specify where your data table is; e.g. Customers!RC12:R999C9(you need to figure out where it is on your sheet).
因为它是一个相对范围。您必须明确指定数据表的位置；例如Customers!RC12:R999C9（你需要弄清楚它在你的工作表上的位置）。
An easy way of figuring this out is highlighting your data table on your worksheet, then switch to the Visual Basic Editor and manually run this (put your cursor inside of the Sub, and press the 'Play' button or go to Run->Run).
解决这个问题的一个简单方法是突出显示工作表上的数据表，然后切换到 Visual Basic 编辑器并手动运行它（将光标放在 Sub 内，然后按“播放”按钮或转到“运行”->“运行” ）。
Sub test2()
Dim r As Range
Set r = Application.Selection
InputBox "your r1c1 range:", , r.Address(True, True, xlR1C1)
End Sub
