Error in argument of Application.WorksheetFunction.Vlookup

Question

Error occur in below VBA line of 'Compile Error: Expected: expression'. Kindly solve where's wrong in line to lookup value from another sheet matched with textbox5

TextBox6.Text = application.WorksheetFunction.VLookup(textbox5.Text,('D:\FolderName[FileName.xlsb]Sheet1'!A2:B12100),2,FALSE)

Answer 1

My apologies for not getting back to you sooner but I have been away for a few days.

I assuming that if the text boxes are on a Userform then your code is in the Userform code module or if the text boxes are on a worksheet then the code is in the worksheets module. If not one of these then I many need to edit the code for you.

AFAIK you cannot reference a closed workbook with worksheet functions in VBA so the workbook needs to be opened. The code first tests if the workbook is open and if not, then opens it.

When using a worksheet function in VBA, the references are set up as in VBA; not as a reference as used on a worksheet with the function. However, it is possible to insert a worksheet function on the worksheet so it is like as if it was entered directly on the worksheet but the following code does not do that.

    Dim wBook As Workbook

    Dim wShtTableArray As Worksheet

    Dim rngTableArray As Range

    On Error Resume Next

    'Test if file already open

    Set wBook = Workbooks("FileNameSource.xlsb")

    On Error GoTo 0

    If wBook Is Nothing Then    'If nothing then not open so open the workbook.

        Set wBook = Workbooks.Open(Filename:="D:\FolderName\FileName.xlsb")

    End If

    Set wShtTableArray = wBook.Worksheets("Sheet1")

    With wShtTableArray

        Set rngTableArray = .Range(.Cells(2, "A"), .Cells(12100, "B"))

    End With

    Me.TextBox6.Text = Application.WorksheetFunction.VLookup(Me.TextBox5, rngTableArray, 2, False)

Answer 2

Sorry, it was problem of paste data, real formula is mention below which occur error.

TextBox6.Text = application.WorksheetFunction.VLookup(textbox5.Text,('D:\FolderName[FileName.xlsb]Sheet1'!A2:B12100),2,FALSE)

Answer 3

Your example formula is missing some of the formula. See Help for Vlookup to get all of the arguments (parameters) for Vlookup.