Posts by Matt W

Adding this line of code between code lines 3 & 4 above solves for the problem.

If n = m Then n = m + 1

Hello. My code below to fill a table (ChartTable1) with the contents of an array works flawlessly so long as there is more than one row of data being moved into the table. However, if the array only contains one row of data, then I receive a subscript out of range error when attempting to fill the table with the array contents.

Dim m&, n&, v, ws As Worksheet
Set ws = ThisWorkbook.Worksheets("ProfileCalc")
m = 2: n = ws.ListObjects("ProfileTable").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
v = Application.Index(ws.Range("A1:M" & n).Value, Evaluate("row(" & m & ":" & n & ")"), Array(1, 13, 11, 9, 2, 3, 4))
This Workbook.Sheets("ChartTable").Range("ChartTable1").Resize(UBound(v, 1), UBound(v, 2)) = v

The last line of code results in the error only when the array tries to fill the table with one row of data. Specifically, hovering over the error reveals UBound(v, 2) = <subscript out of range>.

As the array may contain both single or multiple rows of data, I need to revise the last line of code to handle both conditions without error. Any help is much appreciated!

Thanks!

Thank you, Fluff13. This is exactly what I needed. Incorporating the worksheet object into the filter function allowed me to eliminate ws.Activate. The filter performs accurately this way and my screen flicker is gone. Many thanks!

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?

Dim v, ws As Worksheet
Dim vRows As Variant
Set ws = ThisWorkbook.Worksheets("Sheet1")

ws.Activate
With ws.ListObjects("Table1").DataBodyRange

vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=FALSE,row(#)-~,""X"")", "#", .Columns(35).Address), "~", .Rows(0).row))), "X", False)
v = Application.Index(.Value2, Application.Transpose(vRws), Array(1, 14, 15)

Thanks for any assistance!

That's perfect!! All elements function properly. You've been a huge help in advancing my efforts. Many thanks!

Boom. That's it! Only thing I noticed is that (other than if the first or top item is selected), the output to the range is one-off from the item selected.

I simply can't get that to work. I have to be missing something. Can you put that in the example file and show me that section of the code? I fear I'm over complicating things.

Okay, but when looping through, how do I identify the selected item of the combo box list?

Maybe its because the combo box content I want is in the initialize sub and we're in the comb box change-event sub?

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.

Much cleaner. Works slick. But its still bringing all the combo box items vs. just the selected item.

Missed your last piece before I sent...will try that....

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

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.

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

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!

Dim m&, n&, v, ws As Worksheet        ' data types Long, Long, Variant, WorkSheet
Set ws = ThisWorkbook.Worksheets("Sheet1")
Dim vRws As Variant
Dim r As String
            
19:  r = Me.ComboBox1.Value
20:
21:  ' [1] define Start Row m and Last Row n (based on items in column A) of the ListObjects(1)
22:  m = 2: n = ws.Range("A" & ws.Rows.Count).End(xlUp).row
    
23:  ' [2] assign data to variant datafield array
24:  With ws.ListObjects(1).DataBodyRange
    
25:  vRws = Filter(Application.Transpose(Evaluate(Replace(Replace("if(#=""Group 1"",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)) 'column numbers from DataTable to bring into the listbox
27:  End With

Error problem resolved with the above code.

While creating the example to attach, I ended up solving the problem. I replaced code lines 16 & 17 above with the following, and it resolved the error.

Set Dest = Dest.Resize(UBound(HideArray) - LBound(HideArray) + 1, 1)
    Dest.Value = HideArray