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