Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: VBA Solution To Identify Column Number

    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
    Last edited by JerryDal; 09-15-11 at 04:46. Reason: clarification

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have a look at the if function
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.

    Code:
    Dim lngCol As Long
     
    lngCol = Application.WorksheetFunction.Match("green",Sheet1.Range("1:1"),0)
    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.

  4. #4
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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

  5. #5
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    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.
    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).

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    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.
    Code:
    playList_Col = Application.WorksheetFunction.Match(cboListValue, _
                Sheets("Playlists").Range("1:1"), 0)
    Thanks.
    Jerry
    Last edited by JerryDal; 09-17-11 at 13:39.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •