CODEWITHSUNDEEP
vbaexcel
David Shochet
David Shochet

Reputation: 5395

Search in Excel using VBA

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

Answers (2)

Gary's Student
Gary's Student

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

Dirk Reichel
Dirk Reichel

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

Related Questions