在VLOOKUP问题中使用文件位置 [英] Using a file location in a VLOOKUP issue

问题描述

我从另一个答案中得到了此代码，它比我做的更好，因为您实际上可以选择从中提取文件的位置.但是似乎我无法在VLOOKUP中获得完全正确的文件名?我在VLOOKUP之后收到错误1004.也许还有其他问题.我复制了这段代码，然后替换了所需的内容，但是我需要另一双眼睛.预先感谢.

I got this code from another answer on here and it is better than they way I was doing it because you can actually just select where you are pulling your file from. But it seems like I can't get the file name exactly correct in the VLOOKUP? I get error 1004 right after the VLOOKUP. Maybe there is something else wrong. I copied this code then replaced what I needed but I need another pair of eyes. Thanks in advance.

Dim x As String
Dim lNewBracketLocation As Long

x = Application.GetOpenFilename( _
    FileFilter:="Excel Files (*.xls*),*.xls*", _
    Title:="Choose previous quarter's file", MultiSelect:=False)

MsgBox "You selected " & x
'Find the last instance in the string of the path separator "\"
lNewBracketLocation = InStrRev(x, Application.PathSeparator)
'Edit the string to suit the VLOOKUP formula - insert "["
x = Left$(x, lNewBracketLocation) & "[" & Right$(x, Len(x) - lNewBracketLocation)

Range("V2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($E2,'" & x & "]file_2017072732'!$B$5:$AP$9486,18,FALSE)"
' ERROR 1004
Selection.AutoFill Destination:=Range("V2:V177")
Range("V2:V177").Select

到这一点，它表明x等于"C:\ Name \ Name \ Name \ [Filename.xlsx" .

When I get to that point it shows that x is equal to "C:\Name\Name\Name\[Filename.xlsx".

是应该的格式吗?

推荐答案

问题不在于 x 的值，看起来像是有效格式.

The issue is not with the value of x, which looks like it is in a valid format.

问题在于如何使用其 FormulaR1C1 属性将使用 A1 表示法编写的公式分配给单元格.

The problem lies with assigning a formula, written using A1 notation, to a cell using its FormulaR1C1 property.

更改

ActiveCell.FormulaR1C1 = "=VLOOKUP($E2,'" & x & "]file_2017072732'!$B$5:$AP$9486,18,FALSE)"

任何一个

ActiveCell.Formula = "=VLOOKUP($E2,'" & x & "]file_2017072732'!$B$5:$AP$9486,18,FALSE)"

或

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC5,'" & x & "]file_2017072732'!R5C2:R9486C42,18,FALSE)"

应该没问题.

这篇关于在VLOOKUP问题中使用文件位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！