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!
Code
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
Display More