I have created a piece of code in a userform that will search my specific worksheet and return all the results based on one combobox(cboFunction). The problem i am having is that it is only returning the data that is populated in Column A. I would like the listbox to return all the data in the row based on the search criteria (matching cboFunction's value). Is there something i am missing?
I have pieced this code togetehr from bits and pieces of other searches i have made int he past. But i have been stuck on this issue for the past 4 days and cannot wrap my head around it. Im not particularly fluent in VBA ( i have learned by piecing bits and pieces together over the years).
Any help would be appreciated!!
Private Sub CommandButton01_Click() Dim rngNames As Range Dim arrNames Dim arrResults Dim lngRow As Long If cboFunction.Value = "" Then ListBox1.Clear Exit Sub End If With Sheets("Strategy Raw") Set rngNames = .Range("A5", .Range("A" & Rows.Count).End(xlUp)) End With With rngNames arrNames = Evaluate(.Address & "&CHAR(45)&ROW(" & .Address & ")") End With arrNames = Application.Transpose(arrNames) arrResults = Filter(arrNames, cboFunction.Value) ListBox1.Clear If UBound(arrResults) = -1 Then ListBox1.AddItem "No matches" Else For i = LBound(arrResults) To UBound(arrResults) lngRow = Mid(arrResults(i), InStrRev(arrResults(i), "-") + 1) With ListBox1 .AddItem .List(.ListCount - 1, 0) = Sheets("Strategy Raw").Range("A" & lngRow) .List(.ListCount - 1, 1) = Sheets("Strategy Raw").Range("B" & lngRow) .List(.ListCount - 1, 2) = Sheets("Strategy Raw").Range("C" & lngRow) End With Next i End If End Sub
I cannot upload my document due to work restrictions, but there is currently 10 columns of data for each row of data.