CODEWITHSUNDEEP
excelvba
Leon Lai
Leon Lai

Reputation: 107

Excel VBA: Using FIND: How to select the cell where the item is found?

I am using Excel VBA to search for a string e.g. "CCC" in an Excel worksheet. I used the simple code shown.

However, I want VBA to select the cell where the first occurrence of "CCC" is found. (Just like when you do a FIND manually).

How can I modify my code to achieve this?`

Private Sub CommandButton1_Click()

Dim rng As Range
Set rng = Cells.Find(What:="CCC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

If Not rng Is Nothing Then
    MsgBox ("found")
Else
    MsgBox ("not found")
End If

End Sub

Upvotes: 0

Views: 17071

Answers (3)

Digvijay
Digvijay

Reputation: 406

 Private Sub CommandButton1_Click()
Range("A1").select
Cells.Find(What:="CCC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).activate

activcecell.select
If Not rng Is Nothing Then
    MsgBox ("found")
Else
    MsgBox ("not found")
End If

End Sub

Using activecell.select will select your first find cell.

Upvotes: 2

A.S.H
A.S.H

Reputation: 29332

You can use rng.Select but it needs the worksheet to be active on top (or you habe to activate it first i.e. rng.Parent.Activate). Simplest is to use Application.Goto rng

If Not rng Is Nothing Then
    Application.Goto rng
    MsgBox ("found")
Else
    MsgBox ("Not found")
End If

Upvotes: 3

YowE3K
YowE3K

Reputation: 23974

To select a cell, you can use the Select method. So, if you have a Range object which has been set to the cell you want, you can apply the Select method to that object, i.e.

rng.Select

Incorporated into your existing code, it might look something like:

Private Sub CommandButton1_Click()

    Dim rng As Range
    Set rng = Cells.Find(What:="CCC", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False)

    If Not rng Is Nothing Then
        MsgBox "found"
        rng.Select
    Else
        MsgBox "not found"
    End If

End Sub

Upvotes: 3

Related Questions