Trouble Shooting VBA Vlookup

[Decoderman]

Hello,

I am still new to the vba world and still trying to figure out what will and will not work. I keep getting a "Type Mismatch" error on filez. Please Help!! And Thanks for your time!!

Sub vlook()

Dim strdir$
Dim strfilename$
Dim answer As String
Dim filez As String
Dim rangez As Range
Dim rangez1 As Range


Set rangez = Range("A3", "A" & Range("A3").End(xlDown).Row)
Set rangez1 = Range("A3", "B" & Range("A3").End(xlDown).Row)




strdir = "C:\Documents and Settings\smerdond.ittsys\My Documents\Testing\" 'file path
strfilename = "book*.xls*" 'file name with file path
filez = "'" & strdir & "[" & strfilename & "]" & "Sheet1'!" & rangez1
    
    For Each Cell In rangez

        answer = Application.WorksheetFunction.VLookup(Cell.Value, filez, 2, False)
        Cell.Offset(0, 1).Value = answer

    Next




End Sub

[MarvinP]

Hi Decoderman,

It looks like you are trying to build a string that starts with filez =.

The problem is that you try to append at the very end of this line a Range.

Ranges aren't strings. This gives a type mismatch. My best (first) guess.

[Decoderman]

AHH I see what you mean.

[Decoderman]

new code is

Sub vlook()

Dim strdir$
Dim strfilename$
Dim answer As String
Dim filez As String
Dim rangez As Range


Set rangez = Range("A3", "A" & Range("A3").End(xlDown).Row)




strdir = "C:\Documents and Settings\smerdond.ittsys\My Documents\Testing\" 'file path
strfilename = "book*.xls*" 'file name with file path
filez = "'" & strdir & "[" & strfilename & "]" & "Sheet1'!A:B"
    
    For Each Cell In rangez

        answer = Application.WorksheetFunction.VLookup(Cell.Value, filez, 2, False)
        Cell.Offset(0, 1).Value = answer

    Next




End Sub

[Decoderman]

I get the "Unable to run worksheet function" error... :-/

[MarvinP]

Now your filez is a string and it needs to be a range in the VLookup function.

Study this http://www.functionx.com/vbaexcel/Lesson03.htm

BTW - Edit your last post and put the code in Code Tags instead of Quote Tags.