Error 1004 application-defined or object-defined error using vlookup?

Question

I am getting this error 1004 when trying to use this vlookup. I use a window to select a file then that file is used in the vlookup. I do it in another macro I have and I used basically the same code. But for some reason this one is not working. Can anyone see any glaring issues? I cannot figure out what I am doing wrong.

I get the error on the First VLOOKUP formula right after the "With ws"

Dim iRet As Integer
Dim strPrompt As String
Dim strTitle As String
Dim shtName As String


' Prompt
strPrompt = "Please select the last Kronos Full File before the dates of this Report." & vbCrLf & _
    "For example, if the date of this report is 9-8-17, you would want to use the closest date Kronos Full File." & vbCrLf & _
    "If one was not ran in the past couple days, then run a new Kronos Full File, and then choose that file."

' Dialog's Title
strTitle = "Latest Kronos Full File"

'Display MessageBox
iRet = MsgBox(strPrompt, vbOK, strTitle)

Dim Window2 As String
Dim X As String
Dim lNewBracketLocation As Long
Dim wb2 As Workbook


Window2 = Application.GetOpenFilename( _
    FileFilter:="Excel Files (*.xls*),*.xls*", _
    Title:="Choose the Newest Kronos Full File", MultiSelect:=False)

Set wb2 = Workbooks.Open(Filename:=Window2, ReadOnly:=True)
shtName = wb2.Worksheets(1).name
wb2.Close

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


With ws
.Range("M2").Formula = "=VLOOKUP($K2,'" & X & "]shtName'!$B$2:$E$99999,4,0)"
.Range("N2").Formula = "=VLOOKUP($K2,'" & X & "]shtName'!$B$2:$C$99999,2,0)"
.Range("O2").Formula = "=VLOOKUP($K2,'" & X & "]shtName'!$B$2:$U$99999,20,0)"
.Range("P2").Formula = "=VLOOKUP($K2,'" & X & "]shtName'!$B$2:$Q$99999,16,0)"
.Range("Q2").Formula = "=VLOOKUP($K2,'" & X & "]shtName'!$B$2:$S$99999,18,0)"
End With

Good question I am guessing that is probably what it is, but I copied it over from my other code and those ones work. I will take another look at it. — Robillard
Don't you need .Range("M2").Formula = "=VLOOKUP($K2,'" & X & "]" & shtName & "'!$B$2:$E$99999,4,0)" as shtName is a variable? — SJR
hmm yeah now when I go to debug, the shtName pops up as what the actual sheet name should be now. But I am still getting the error — Robillard
@Robillard no! the debugger is your primary tool, learn to use it! You can toggle a breakpoint on any executable statement with F9. When you run the code, it will stop there. Then you can hit F8 to run the code line by line, use the immediate pane (Ctrl+G) to test things and reassign in-memory values on the fly, use the Locals toolwindow to view the values for everything that's currently in-scope, ... don't just wipe out your code and rewrite it, learn to use the debugger, it's fundamental! — Mathieu Guindon

Shai Rado Shai Rado · Accepted Answer · 2017-10-22T10:36:34

Another way to go around using a Range's address from another workbook, is set the range, and later on you can use Range.Address(True, True, xlR1C1, xlExternal). The 4th partameter will add the name of the worksheet and workbook if necessary.

Dim Rng1 As Range  ' new Range Object

Window2 = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*),*.xls*", _
                                Title:="Choose the Newest Kronos Full File", MultiSelect:=False)

Set wb2 = Workbooks.Open(Filename:=Window2, ReadOnly:=True)
'shtName = wb2.Worksheets(1).Name '<-- not necessary

Set Rng1 = wb2.Worksheets(1).Range("B2:E99999")    
wb2.Close

With ws
    .Range("M2").Formula = "=VLOOKUP($K2," & Rng1.Address(True, True, xlR1C1, xlExternal) & ",4,0)"
    ' define more ranges for the other formulas

End With

Error 1004 application-defined or object-defined error using vlookup?

2 Answers