vba 使用单元格值作为 VLOOKUP 表数组的参考？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17603556/
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
Use Cell Value as reference for VLOOKUP table array?
提问by user2574262
I would like to be able to allow a user to change the file being used as a reference in a VLOOKUP. All the files being used will have the same array, just different files paths.
我希望能够允许用户更改在 VLOOKUP 中用作参考的文件。使用的所有文件都将具有相同的数组，只是文件路径不同。
This is how i think it should operate.
这就是我认为它应该运行的方式。
- Click macro button that opens file browser.
- Allow user to choose file.
- Paste file path in cell (A1)
- Convert path to include array.
- In VLOOKUP formula, reference the cell A1 as the 'table array'.
- 单击打开文件浏览器的宏按钮。
- 允许用户选择文件。
- 在单元格 (A1) 中粘贴文件路径
- 将路径转换为包含数组。
- 在 VLOOKUP 公式中，将单元格 A1 引用为“表格数组”。
I am already able to complete up to step 3. Any idea on how to add the defined array and reference the cell in a VLOOKUP?
我已经能够完成第 3 步。关于如何添加定义的数组并在 VLOOKUP 中引用单元格的任何想法？
回答by MakeCents
I would just record doing exactly what you want to do and then replace what you did with a variable that gets assigned, like when you place the path into cell A1. Then just have the macro re-write the appropriate vlookup formula.
我只会记录你想做的事情，然后用一个分配的变量替换你所做的事情，比如当你将路径放入单元格 A1 时。然后只需让宏重新编写适当的 vlookup 公式即可。
You could also make a formula that would show what you want, and then use vba to copy that and paste values. You may then need to find and replace something in the cell(s) to get it to actually convert to a formula.
您还可以制作一个公式来显示您想要的内容，然后使用 vba 复制并粘贴值。然后，您可能需要查找并替换单元格中的某些内容，以使其实际转换为公式。
Example:
例子：
In A1 Put the path (with workbook name, i.e.C:\Users\username\Desktop\Book3.xlsx )
In B1 ="=VLOOKUP(D1,'"&MID(A5,1,FIND("*",SUBSTITUTE(A5,"\","*",LEN(A5)-LEN(SUBSTITUTE(A5,"\","")))))&"["&MID(A5,FIND("*",SUBSTITUTE(A5,"\","*",LEN(A5)-LEN(SUBSTITUTE(A5,"\",""))))+1,100)&"]Sheet1'!B:C,2,FALSE)"
B1 then looks like this =VLOOKUP(D1,'C:\Users\username\Desktop\[Book3.xlsx]Sheet1'!B:C,2,FALSE)
Copy B1 to C1 and do a find replace of something, like find Users and replace all with Users.
This is messy, but I use something like this for a tool that needs to update form 30 different files of different sizes based on 6000 rows of array formulas that update of yet another spreadsheet. It works well for me, but a bit slow for large files.
这很混乱，但我使用类似这样的工具来更新 30 个不同大小的不同文件，该文件基于 6000 行数组公式更新另一个电子表格。它对我来说效果很好，但对于大文件来说有点慢。
