Posts by Matt W

    Hello. I use the Filter Function below to filter a column in a Table and then populate a List Box with the results. I have found that I must activate the worksheet with the table in order for the function to filter correctly. But activating the worksheet causes the screen to flicker. Is there a way to avoid activating the worksheet yet have the Filter Function still filter correctly? Alternatively, can I avoid the screen flicker caused by activating the worksheet?

    Code
    1. Dim v, ws As Worksheet
    2. Dim vRows As Variant
    3. Set ws = ThisWorkbook.Worksheets("Sheet1")
    4. ws.Activate
    5. With ws.ListObjects("Table1").DataBodyRange
    6. vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
    7. v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)


    Thanks for any assistance!

    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....

    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.

    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

    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


    Code
    1. 25: vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#="" & r & "",row(#)-~,""X"")", "#", .Columns(37).Address), "~", .Rows(0).row))), "X", False)
    2. 26: v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 11, 12, 13, 29, 30, 34, 42))

    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!


    Hello. I'm trying to transfer the single column list contents from a user form list box into an array, and then into a single column range on a worksheet. The code line (16) to resize the destination range throws an "application-defined or object-defined error" that I cannot figure out. I'm in over my head on this one and hoping someone can help me diagnose my problem. Many thanks!