Is there a one-line solution in Excel VBA to identify which column in row #1 contains a specific value?

Example: Row 1, columns A,B,C contain "red" "green" "blue". In code, when a variable is assigned the value "green" the value 2 needs to be returned, which represents column #2.

I need something similar to the MATCH worksheet function =MATCH("green",A1:C1), but maybe the forum can advise me that the only way to do this is with a loop that examines each value in row #1.

I have a combo box of headings from a worksheet for only those headings where the column contains data below the headings. When a combo list item is chosen, in the combo box change event the code will examine the specific column and get the number of items (populated cells) under the column heading.

Thanks.
Jerry

have a look at the if function

There's certainly nothing wrong with looping through the row to find the matching value. If you assign the values from the row into an array and loop through the array, it will be a bit faster than looping through the range objects themselves.

If you want to use the Match() worksheet function in VBA then you can. It's accessed via the Application.WorksheetFunction class. I do recommend that you use an exact match though - the formula example you gave is an approximate match.

Note that if the word "green" is not found then a runtime error will occur, so you'll need to include the appropriate error handling.

The Application.WorksheetFunction.Match solution from Colin is the one I will use, and it works.

The combo box values are taken from the same row that the Match function uses, so I do not see that error checking is necessary. I used the combo Change event to do one thing--take the focus off the combo box, which triggers the combo AfterUpdate event where the Match function is used.

I observed that the AfterUpdate event only executes one time on a combo box while it has the focus. For my purpose, I want to present information immediately when a different list item is selected, so removing the focus makes this possible.

Jerry
RESOLVED

If they are taken from the same source, then doesn't the Combobox's ListIndex give you the position you need? Just bear in mind that the ListIndex is 0 based (-1 means no item from the list has been selected).

To briefly explain why I am not using a combo ListIndex for Excel column identification, the application includes playing music files and generating playlists.

A utility/form to merge one music playlist into another displays playlist names in a MERGE_FROM combo and a MERGE_TO combo. The FROM side does not display playlists that are currently empty. When a merge is executing, the worksheet column for the playlist chosen in the FROM combo has to be identified. There are also two buttons to view detail that make a list box visible to view the music titles associated with the playlist name in the two combos, and your solution is also used in this view function to identify the column where the data resides.

Because the FROM combo may not include every column from a worksheet of playlists due to a playlist being empty, I can't use the combo ListIndex on the FROM side.

I was curious about a shorter VBA method to identify a column than one using a loop, and you provided me the one-line solution.
Thanks.
Jerry