Lookup Last Match of a ComboBox value - VBA

[CakzPrimz]

Hello,

I want to extract the last driving kilometer of a vehicle ID, let's say 27001 thru a ComboBox.
The database is store in sheets "Data", and the number of vehicle ID is in column C while the driving kilometer is in column G.
So, when I change the vehicle ID in ComboBox1 then it will lookup the last driving kilometer in TextBox8.

Below the code I have which not work.

Private Sub ComboBox1_Change()
'TextBox8.Value = Application.Lookup(2, 1 / Worksheets("Data").Range("R4C7:R23C7") = Me.ComboBox1.Value, Worksheets("Data").Range("R4C5:R23C5"))
End Sub

Please help me to fix it.
Thanks

 

[steve the fish]

Hi. See if this works:

TextBox8.Value = Evaluate("=Lookup(2,1/(Data!G4:G23=""" & Me.ComboBox1.Value & """),Data!E4:E23)")

 

[CakzPrimz]

Hi. See if this works:

TextBox8.Value = Evaluate("=Lookup(2,1/(Data!G4:G23=""" & Me.ComboBox1.Value & """),Data!E4:E23)")

Hi Steve,

Thanks for the response, when I ran the code it gave me
"run time error. Could not set the Value property. Type mismatch".

When I checked the type of vehicle ID is number, not text. Is this triggering the error above?
What should I do, do I have to change the type from number into text type?

Best regards

 

[steve the fish]

Try:


TextBox8.Value = Evaluate("=Lookup(2,1/(Data!G4:G23=" & Me.ComboBox1.Value & "),Data!E4:E23)")

 

[CakzPrimz]

Hi Steve,

Thanks again for your assistance, I still got the same error with this code below:

[COLOR=#574123]TextBox8.Value = Evaluate("=Lookup(2,1/(Data!G4:G23=" & Me.ComboBox1.Value & "),Data!E4:E23)")[/COLOR]

Thanks

 

[steve the fish]

Im going to say that the combobox1.Value is not found in the range Data!G4:G23. That would produce an error. You could stop the error manifesting in the runtime error like this for example:

TextBox8.Value = ""
x = Evaluate("=Lookup(2,1/(Data!G4:G23=" & Me.ComboBox1.Value & "),Data!E4:E23)")
If Not IsError(x) Then
    TextBox8.Value = x
End If

 

[CakzPrimz]

Dear Steve,

Thanks so much for your help and assistance, based on your suggestion I made this changes and now it works !!! Problem solved.

TextBox8.Value = Evaluate("=Lookup(2,1/(Data!E4:E23=""" & Me.ComboBox1.Value & """),Data!G4:G23)")

Again, thank you Steve and MrExcel