vlookup problem for comboboxes

[maabadi]

Hi
What is problem with this code

It show error: Run time error: "-2147352571 (80020005)":
Could not set the value property. Type mismatch

Private Sub ComboBox1_AfterUpdate()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet2")
    Me.ComboBox2.Value = Evaluate("VLOOKUP(""" & Me.ComboBox1.Value & """,'Sheet2'!B:C,2,FALSE)")
End Sub

I don't define any code for combobox2 and when I click on it , Show error

thanks

 

[portews]

It looks like too many quotes in your Evaluate statement. You're not looking for the literal "Me.ComboBox1", you're looking for what that variable represents, so you don't want to be quoting it, it should stay outside the quotes. Same for the Sheet2, no single quotes.

Try this:

Me.ComboBox2.Value = Evaluate("VLOOKUP(" & Me.ComboBox1 & ",Sheet2!B:C,2,FALSE)")

 

[maabadi]

It looks like too many quotes in your Evaluate statement. You're not looking for the literal "Me.ComboBox1", you're looking for what that variable represents, so you don't want to be quoting it, it should stay outside the quotes. Same for the Sheet2, no single quotes.

Try this:

Me.ComboBox2.Value = Evaluate("VLOOKUP(" & Me.ComboBox1 & ",Sheet2!B:C,2,FALSE)")

thanks.
it works .