Searching table column for a value and populating an array with the matching row contents

  • Looking for an assist to adjust this code sourced by Forum Guru Fluff13. As presented below, lines 25 & 26 successfully searches column 37 of my data table for the string "Group 1" and returns the rows and desired columns into array v.

    I need to adjust line 25 to search for the selected value of ComboBox1 on a user form, instead of just "Group 1". CombBox1 holds a list of text strings (Group 1, Name 3, Team 7, etc). I dimmed r as a string variable to hold the combobox value, but I get a type mismatch error when trying to replace the "Group 1" text string with the variable r...with or without quotes or any other variable type. Any help would be much appreciated!

  • Possibly...

    vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#= & r ,row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).Row))), "X", False)

    If I've been helpful, let me know. If I haven't, let me know that too. 

  • Maybe

    vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=" & r & ",row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).Row))), "X", False)
  • Thanks, Fluffinator! Attempted both options above, but both still result in error 13 type mismatch. On a whim, added double quotes as in line 25 below. This pushed the type mismatch error down to the next row (line 26). During debugging, I highlighted this portion of the code: "" & r & "" to see its result, and it revealed "" & r & "" = "Group 1", where Group 1 is the combo box drop down selection I made. So seems we are very close!

    Debugging in Watch window for line 26 reveals Value = <Expression not defined in context> and Type = Empty

    25: vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#="" & r & "",row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).row))), "X", False)
    26: v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 11, 12, 13, 29, 30, 34, 42))
  • FYI...went back to single quotes " & r & " as you originally suggested, and debugging revealed r = "Group 1" with the type mismatch error remaining on line 25. So double quotes is not a thing...I think.

  • Thank you, Fluff13. I've attached a sample file. Code errors occur on lines 25 and 125 of my combo box change event sub.

    The goal is to populate the list box with specific records from the table based on the combo box selection AND move the contents of the combo box array to a designated range on the worksheet.

    Some of my challenge is I'm finding combo boxes have different properties than list boxes in some cases and I'm not finding an intuitive work around.

    Thanks for any help you can provide!

    ComboBox to Array Test File.xlsm

  • Not sure how you get an error on line 25 as there is a compile error as soon as you select anything from the combo.

    That said you will need to use

    25:         vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(left(#,7)=""" & q & """,row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).row))), "X", False)
                v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 17, 18)) 'column numbers from RiskDataTable to bring into the combobox
  • Thanks for your assist on this Fluff13....your time and effort is much appreciated. Did that piece of code work for you in the example file? I swapped it in and it pushed the type mismatch error down to the next line (v = Application.Index...) and the variable q results to "0" rather than the combo box selection.

    As in the original code at the top of this post, if I use a specific text string to search for (such as "Group 1"), the combo box works like a charm and the associated items are brought into the list box. So I know its close. Just can't seem to solve for use with a variable.

    Line 125 is also a problem. Not sure how to represent the "selected item" of the combo box in the If/Then statement, as .Selected(row) doesn't work the same as for a listbox.

  • Line 125 is also a problem. Not sure how to represent the "selected item" of the combo box in the If/Then statement, as .Selected(row) doesn't work the same as for a listbox.

    I have no idea what you are trying to do with that portion of the code.

  • At a guess replace all that last section with

                        With wksDest.Range("GroupImportRange")
                            .Resize(UBound(v), UBound(v, 2)).Value = v
                        End With
  • That last bit did the trick! Thanks much for that!

    As for line 125, I'm trying to paste ONLY the array contents for the combo box "selected" item into the worksheet, rather than the contents for all the items in the combo box. Its the If/Then statement that's the problem, as I can't figure out how to isolate just the selected item. ListIndex doesn't work, Value doesn't work, Selected doesn't work....using the variable q doesn't work. I must be missing something....

  • Those are the list box items (from array v). What I need to appear there is a single row containing the column items from the combo box (array g) for just the one selected item. That combo box array is populated when the form is initiated and captures 8 columns of data. I need those 8 columns to appear on the worksheet for the combo box item that is selected.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!