Vlookup On A Userform With Textboxes

[chunu]

Hi,
I want to use vlookup in userform textbox,below code is working in current workbook.

Private Sub TextBox1_Change()TextBox48.Value = Application.WorksheetFunction.VLookup(CLng(TextBox1.Text), Worksheets("product").Range("a2:d100"), 4, False)
End Sub

But i want to use with closed workbook (g:\data.xlsm)
I tried below code but failed, run-time error '9':Subscript out of range

TextBox48.Value = Application.WorksheetFunction.VLookup(CLng(TextBox1.Text), Application.Workbooks("data.xlsm").Worksheets("product").Range("a2:d20"), 4, False)

Thanks

 

[Domenic]

For a closed workbook, you can use the ExecuteExcel4Macro method. Notice that the range is specified as a R1C1 string, not an A1 string, as required.

    Dim sourceFolder As String
    Dim sourceFileName As String
    Dim sourceSheetName As String
    
    sourceFolder = "c:\users\domenic\Desktop\" 'change the path accordingly
    sourceFileName = "data.xlsm"
    sourceSheetName = "product"
    
    TextBox48.Value = ExecuteExcel4Macro("VLOOKUP(" & CLng(TextBox1.Text) & ",'" & sourceFolder & "[" & sourceFileName & "]" & sourceSheetName & "'!R2C1:R20C4,4,FALSE)")

Here's the Microsoft document on the ExecuteExcel4Macro method...

https://docs.microsoft.com/en-us/office/vba/api/excel.application.executeexcel4macro

Hope this helps!

 

[chunu]

***wow****
Thank you, really appreciate.

 

[Domenic]

You're very welcome!

Cheers!

 

[chunu]

Hi,
can i use index match instead of vlookup in textbox with ExecuteExcel4Macro method.
this my formula

INDEX('g:\[data.xlsm]product'!I:I,MATCH(A2,INDEX(('g:\[data.xlsm]product'!A:A),0,1),0))

Thanks

 

[Domenic]

Yes, it is possible. However, with your example, VLOOKUP would suffice. Also, the second INDEX function isn't really needed. In any case, your INDEX/MATCH formula would be written as follows...

TextBox48.Value = ExecuteExcel4Macro("INDEX('" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C9:C9,MATCH(" & range("A2").Value & ",INDEX('" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C1:C1,0,1),0))")

However, to match text values, the match value will need to be enclosed withing quotes...

TextBox48.Value = ExecuteExcel4Macro("INDEX('" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C9:C9,MATCH(""" & range("A2").Value & """,INDEX('" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C1:C1,0,1),0))")

Actually, it could be written this way...

    Dim sourceFolder As String
    Dim sourceFilename As String
    Dim sourceWorksheet As String
    Dim lookupRangeReference As String
    Dim returnRangeReference As String
    
    sourceFolder = "c:\users\domenic\Desktop\"
    sourceFilename = "sample.xlsm"
    sourceWorksheet = "Sheet1"
    lookupRangeReference = "'" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C1:C1"
    returnRangeReference = "'" & sourceFolder & "[" & sourceFilename & "]" & sourceWorksheet & "'!C9:C9"
    
    TextBox48.Value = ExecuteExcel4Macro("INDEX(" & returnRangeReference & ",MATCH(" & Range("A2").Value & ",INDEX(" & lookupRangeReference & ",0,1),0))")

 

[chunu]

Hi,
Thanks for your help, i tried index match formula, i think vlookup is better.
But i am facing one issue with vlookup it is not searching text (with number it is fine)

[COLOR=#333333]TextBox48.Value = ExecuteExcel4Macro("VLOOKUP(" & CLng(TextBox1.Text) & ",'" & sourceFolder & "[" & sourceFileName & "]" & sourceSheetName & "'!R2C1:R20C4,4,FALSE)")[/COLOR]

Need your help
Thanks

 

[Domenic]

In that case, instead of converting your textbox value into a Long using CLng, simply wrap it within quotes...

[COLOR=#333333]TextBox48.Value = ExecuteExcel4Macro("VLOOKUP(""" & TextBox1.Text & """,'" & sourceFolder & "[" & sourceFileName & "]" & sourceSheetName & "'!R2C1:R20C4,4,FALSE)")[/COLOR]

 

[chunu]

Thank you so much

 

[Domenic]

You're very welcome!

Cheers!