Help with excel macro for Vlookup

[stzruya]

Hi,
I'm trying to write code of vlookup function that will refer to sheet name by name that will be set by string input. meaning that based on the string value the vlookup will go to the sheet name that exist in the string.

Thank you in advance
stzruya

 

[Webbarr]

Well a VBA solution could work something like this -

Public Function fnMyVlookupResult(varLookupVal As Variant, strSheetName As String) As Variant    
fnMyVlookupResult = WorksheetFunction.VLookup(varLookupVal, Sheets(strSheetName).Range("A1:B3"), 2, False)
End Function

But if it doesn't have to be a VBA solution, I would imagine it's easier to use the INDIRECT formula.

Here is an example of Indirect()

 

[stzruya]

Thanks for the help
I need to refer the Vlookup to sheet in specific workbook (different than the one that the macro is running on). i defined the workbook location and name as variable.
How can i do the vlookup for sheet in other workbook - where do i add/write the workbook variable and the sheet name as variable too (variable for sheet name defined as string).

** i do need it in VBA

Appreciate your support

 

[Webbarr]

No worries, hope this next solution works for you.

Public Function fnMyVlookupResult(varLookupVal As Variant, strWBName As String, strSheetName As String) As Variant
    Dim wbLookIn As Workbook
    
    On Error GoTo err_handle
    
    Set wbLookIn = Workbooks.Open(strWBName)
    
    With wbLookIn
        fnMyVlookupResult = WorksheetFunction.VLookup(varLookupVal, .Sheets(strSheetName).Range("A1:B3"), 2, False)
        .Close
    End With
    Exit Function
    
err_handle:
    Select Case Err
        Case Is = 1004
            fnMyVlookupResult = "#ERROR"
            Exit Function
        Case Else
            Err.Raise Err, "fnMyVlookup", Err.Description, Err.HelpFile, Err.HelpContext
            Resume Next
    End Select
End Function

This function will open the workbook for you & perform the VLookup in the specified range A1:B3 of whatever sheet you input into the strSheetName param.

• 2nd parameter has changed to the name of the workbook you want to do the vlookup() in
• If your .xlsm file is saved inside the same directory as the file you want to look up in, you can get away with just using the WB name (including extension .xls(x/m), if it's not then you will need to use the full file path
• For your own purpose you may want to control the workbook being opened / closed outside of the function if you're performing this multiple times in one go
• You may want to obviously make the range a bit more dynamic

Hope this has helped, let me know!

 

[stzruya]

Hi,
Want to let you know that it's worked like a charm , i Just finished to write the full code i needed and it was perfect. i also use your recommendations to open the workbook outside the function.
Thank you for the great support.

 

[Webbarr]

Glad it helped!