我是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)
任何帮助都将不胜感激。
谢谢!
path
实际上的值了吗?试着在给path
赋值的那一行打一个断点,看看它的值是多少。听起来它的值是不正确的。 - Martin