Lookup Last Match of a ComboBox value - VBA

[Prima Satria]

Hello,

I want to extract the last driving kilometer of a vehicle ID, let's say 27001 thru a ComboBox1.
The database is store in sheets "Data", and the number of vehicle ID is in column F while the driving kilometer is in column O.
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 click DATA INPUT button in sheet "DATA"

Thanks in advance.

 

[NARAYANK991]

Hi ,

Your VBA project is password protected.

Narayan

 

[Prima Satria]

Really sorry, below the password :

p@ss_W0rd+Exc3L*cAkZ?pR1mA=SuRy4&$

Dhanyavaad

 

[NARAYANK991]

Hi ,

The problem is in these two lines of code :

TextBox8.Value = Evaluate("=Lookup(2,1/(Data!O4:O23=""" & Me.ComboBox1.Value & """),Data!F4:F23)")

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

The highlighted portion will resolve to the vehicle ID 27001 , 27002 ,.... ; your worksheet tab named Data has this field in column F ; using any other column , such as G or O , will result in an error value being returned , since no match will be found.

Correct the references to refer to column F.

Narayan

 

[Prima Satria]

I changed the code into this, and running well.

TextBox8.Value = Evaluate("=Lookup(2,1/(Data!F4:F23=""" & Me.ComboBox1.Value & """),Data!O4:O23)")

Thanks so much for your help. I really appreciate it.
Chandoo is the best !!!