VBA - Go to cell based on cell address in another cell

[nx4737]

Good morning, I've always found the advice posted here useful so I'm hoping someone can help me out!

I know a bit about VBA and have coded a few macros, but I'm really struggling to find a way to do this one.

Basically, in column A, I have a very large list of data, I'd like a VBA macro that if the contents of cell B2 are found in a cell in column A, that cell then becomes the active cell.

So if the contents of cell B2 are found in cell A123, cell A123 becomes the active cell.

I know I could just use ctrl+F, but I need cell B2 to be a search box.

Thanks in advance for any help!

 

[abdelfattah]

Hi,
you can use this code.

Sub Find_First()
Dim FindString As Variant
Dim Rng As Range
FindString = Range("b2").Value
If Trim(FindString) <> "" Then
    With Sheets("Sheet1").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True 'value found
        Else
            MsgBox "Nothing found" 'value not found
        End If
    End With
End If
End Sub

 

[nx4737]

That's great, thanks, I'll give it a go!