我是Excel宏vba中的新手。我的vlookup代码存在问题,该代码引用了用户选择的另一个工作簿。
这是我的代码:
Private Sub vlookups()
Dim data_file_new As String
Dim i As Integer
Dim a As String, b As String, path As String
data_file_new = CStr(Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", Title:="Select new data file for VLOOKUP"))
path = data_file_new
a = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AB,28,0)"
b = "=VLOOKUP(A:A,'[" & path & "]Source'!$A:$AJ,36,0)"
i = 7
Do Until Sheets("Macro Template").Cells(i, 1) = ""
Sheets("Macro Template").Cells(i, 37) = a
Sheets("Macro Template").Cells(i, 38) = b
i = i + 1
Loop
End Sub
我的问题是我的代码没有为vlookup提供正确的公式。相反,它给出以下公式:
=VLOOKUP(A:A,'[E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source]No Approval Monitoring Log_June'!$A:$AB,28,0)
正确的公式是这样的:
=VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)
任何帮助,将不胜感激。
谢谢!
试试这个(未经测试)
Private Sub vlookups()
Dim ws As Worksheet
Dim lRow As Long
Dim sPath As String, sFile As String, sTemp As String
Dim Ret
Set ws = ThisWorkbook.Sheets("Macro Template")
Ret = Application.GetOpenFilename(FileFilter:="Excel Workbooks (*.xls*),*.xls*", _
Title:="Select new data file for VLOOKUP")
If Ret = False Then Exit Sub
sFile = GetFilenameFromPath(Ret)
sPath = Split(Ret, sFile)(0)
sTemp = "=VLOOKUP(A:A,'" & sPath & "[" & sFile
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
.Range("AK7:AK" & lRow).Formula = sTemp & "]Source'!$A:$AB,28,0)"
.Range("AL7:AL" & lRow).Formula = sTemp & "]Source'!$A:$AJ,36,0)"
End With
End Sub
Public Function GetFilenameFromPath(ByVal strPath As String) As String
If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
GetFilenameFromPath = _
GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function
说明:
Application.GetOpenFilename()
返回Variant
。如上面的代码所示处理它。
您正在寻找的公式=VLOOKUP(A:A,'E:\AP NO APPROVAL\[No Approval Monitoring Log_June 2015 xlsx.xlsx]Source'!$A:$AB,28,0)
,并Ret
会给你一个直接的文件路径和名称E:\AP NO APPROVAL\No Approval Monitoring Log_June 2015 xlsx.xlsx
。在文件名Vlookup
前后放置一个[]
。您必须首先从文件路径中提取文件名,然后重建整个字符串。我们Function GetFilenameFromPath
在上面的代码中使用来检索该代码。
您无需循环单元格即可输入公式。您可以在整个范围内的“一次执行”中输入公式。
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句