CODEWITHSUNDEEP
vbaexcel
NsD
NsD

Reputation: 69

How to use a variable as a range in VLOOKUP inside VBA Evaluate?

I don't know if I explained this too well. I'm a beginner and I'm trying to get a value from a file using VLOOKUP in VBA. However, even though I can apparently work with the string itself, I cannot use it as a variable.

So, the idea is to automatically populate two text boxes when I select something in the dropdown. The dropwdown itself determines the Worksheet that has the data.

Private Sub cbProductList1_Change()
    vCode1 = Application.WorksheetFunction.VLookup(cbProductList1.Value, 
    [Products!A:B], 2, False)
     Me.tbProdCode1 = vCode1
    vPrice1 = Evaluate("VLOOKUP(" & vCode1 & ", " & Me.labelCFValue & ", 2, False)")
     Me.tbPrice1 = vPrice1
End Sub

If I run a MsgBox on vCode1 - it gives me the string that needs to be the first argument for VLOOKUP. If I run a MsgBox on Me.labelCFValue it gives me CF_1!A25:B33 (without the quotes) just as I need it to do. But when I run MsgBox on vPrice1, I get an error.

Later Edit: Alternatively, if you could help me use Me.labelCFValue inside Application.WorksheetFunction.VLookup(), that could also be good.

Please help?

Upvotes: 1

Views: 962

Answers (1)

Variatus
Variatus

Reputation: 14383

I was unable to test the code but believe that this should either work or help you find your way.

Option Explicit

Private Sub cbProductList1_Change()

    Dim Rl As Long                              ' last row
    Dim LookupRange As Range
    Dim Sp() As String                          ' splitting labelCFValue
    Dim vCode1 As String
    Dim vPrice1 As Double

    ' ActiveSheet is the default, but better to declare
    With ActiveSheet
        Rl = .Cells(.Rows.Count, "A").End(xlUp).Row
        Set LookupRange = .Range(.Cells(1, 1), Cells(Rl, 2))
    End With
    vCode1 = Application.VLookup(cbProductList1.Value, LookupRange, 2, False)
    Me.tbProdCode1 = vCode1

    ' If Me.labelCFValue is a Label, the string should be its Caption property.
    ' If it is a Textbox the string should be its Value or Text property.
    ' Either way, it is better to specify what you are addressing:-
    Sp = Split(Me.labelCFValue, "!")
    Set LookupRange = Worksheets(Sp(0)).Range(Sp(1))
    vPrice1 = Evaluate(Application.VLookup(vCode1, LookupRange, 2, False))
    Me.tbPrice1 = vPrice1
End Sub

Consider adding some precautionary code to deal with the possibility that either of the Vlookups return an error.

Upvotes: 2

Related Questions