How to use vlookup properly in VBA

[gorgon777]

Hi,

I have the below code for a Loop

Do While strCurrentFile <> ""
    If strCurrentFile <> "No_PO_Expense.xlsm" Then
        'Create Excel file in memory
        Dim XL As Excel.Application
        Dim WBK As Excel.Workbook
        Dim result As String
        Dim a As String
        Dim myRange As Range
        Set XL = CreateObject("Excel.Application")
        Set WBK = XL.Workbooks.Open(filePath & strCurrentFile)
        a = WBK.Sheets("Expenses").Range("M2")
        Set myRange = ThisWorkbook.Sheets("TowerList").Range("A2:A61")
        'MsgBox WBK.Sheets("Expenses").Range("A2").value
        result = WBK.Application.WorksheetFunction.VLookup([a], [myRange], 4, False)
        'folderName = WBK.Sheets("Expenses").Range("M2") & " " & WBK.Sheets("Expenses").Range("O2")
        folderName = WBK.Sheets("Expenses").Range("M2") & " " & result
            With CreateObject("Scripting.FileSystemObject")
                If Not .FolderExists(filePath & folderName) Then .CreateFolder (filePath & folderName)
            End With
        DoEvents
        WBK.Application.Quit
        XL.Application.Quit
        DoEvents
        FileCopy filePath & strCurrentFile, filePath & folderName & "\" & strCurrentFile
        strCurrentFile = Dir
    End If
Loop

a - this is a String text to search for.
myRange - this is the range I am looking in.

However, I get an error saying "Unable to get the Vlookup property of the Worksheet function class" on the line where the Vlookup occurs. Have i done something wrong with the syntax?

 

[Jerry Sullivan]

Try...

result = Application.VLookup(a, myRange, 4, False)

 

[Norie]

Dim Result as Variant instead of String and remove Worksheets as JS411 suggested.

Oh, and I really don't think there's a need to create a new instance of Excel for this.

Just open the file in the current instance with Workbooks.Open.