Select combobox item using string matching

Hi, this is my first post to this forum. I have a reasonable level of VBA skill, but this one escapes me. Hopefully some seasoned pro can help.

I have a 2-column combobox on a UserForm, which is populated from a named range in another worksheet. When the UserForm is opened, I want the selected item in the combobox to be based on a string match with a value taken from a textbox in the worksheet, with the string matched to the second column in the combobox.

So for example, if the values in my combobox are:

1, Apple
2, Pear
3, Orange

and the value from my textbox is "Orange", I would like to have row 3 selected in my combobox when the form opens.

I can do this for a one-column combobox using Application.Match and Application.Transpose(ComboBox1.List) (see the code snippet below), but can't get this to work when searching on the second column of the combobox (runtime errors and type mismatches galore).

Please Login or Register  to view this content.

I'd really appreciate any help that anyone can provide - this has me tearing my hair out!