Hello,
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!!
CODE:
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 SubI cannot upload my document due to work restrictions, but there is currently 10 columns of data for each row of data.