CODEWITHSUNDEEP
vbaexcel
Robillard
Robillard

Reputation: 117

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

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

Upvotes: 0

Views: 493

Answers (2)

Robillard
Robillard

Reputation: 117

It seems like my issue had to do with the range that I was trying to use the VLOOKUP with. It looks like once I changed the 99999 to only like 9999, then it seemed like the VLOOKUP worked. I am still not sure why but I am pretty sure that was it. I got no error message when I lowered that number range. I am guessing because it was going out of the ranges of the actual worksheet or something.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

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

Upvotes: 1

Related Questions