Reputation: 5395
I need to search a worksheet by a particular value in a specific column. I have to do something with values in other columns of the found rows. What is the most simple and efficient way to get all row numbers that have the search value in that specific column?
Thanks.
Upvotes: 0
Views: 100
Reputation: 96791
If you are looking for happiness in some region of a worksheet, the select that region and run:
Sub FindingHappiness()
Dim s As String, rng As Range, r As Range
Dim msg As String
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
s = "happiness"
For Each r In rng
If InStr(1, r.Text, s) > 0 Then
msg = msg & vbCrLf & r.Row
End If
Next r
MsgBox msg
End Sub
Note that using this technique will allow you to search in a single row, or in a single column, or in a block of cells, or all the cells on a worksheet, or even in a disjoint group of cells.
Upvotes: 0
Reputation: 7979
You could try something like that:
Public Function Test(str As String, rng As Range) As Variant
Dim xVal As Variant, Arr() As Variant
Dim i As Long
ReDim Arr(0 To 100)
For Each xVal In rng
If xVal.Value = str Then
Arr(i) = xVal.Row
i = i + 1
End If
Next
If i Then
ReDim Preserve Arr(0 To i - 1)
Test = Arr
Else
Test = 0
End If
End Function
(Done by phone. May contain errors.)
Upvotes: 1