How to find cell containing string in entire worksheet

Question

I would like to find a cell in a worksheet containing a specific string.

I won't know precisely how many columns or rows there will be in the spreadsheet, hence why I wanted to do it with CurrentRegion.

This is what I was trying:

=FIND("Data String", Range("A1").CurrentRegion)

Answer 1

You should have a look into the Microsoft References: Range.Find Method (Excel).

 .Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

Example:

Dim rngFound as Range
With Worksheets("MySheetName").Cells
    Set rngFound  = .Find("MySearchString", LookIn:=xlValues)
    If Not rngFound Is Nothing Then 
        'something is found
    else
        'nothing found
    End If
End With

searches the whole sheet

Answer 2

Try This

 FindString = Sheets("Sheet1").Range("D1").Value

---------- This will select the next Cell in range with the inputbox value

Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
    With Sheets("Sheet1").Range("A: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
        Else
            MsgBox "Nothing found"
        End If
    End With
  End If
End Sub

Find Value