Get the reference of a cell containing a certain t

Question

I have somewhere in my worksheet a cell containing a specific text (for exemple "hello") and I want to know which cell is it. I want to know the reference of this cell (for exemple "B52").

I tried some things with the INDEX or MATCH formula but I'm quite confused about how should I do for this kind of query.

It would be great if someone could give some tips.

Thank's in advance !

Answer 1

Try this small macro:

Sub WhereIs()
    Dim r As Range
    For Each r In ActiveSheet.UsedRange
    If InStr(1, r.Text, "hello") > 0 Then
        MsgBox r.Address
        Exit Sub
    End If
    Next r
End Sub

EDIT#1

Here is the same approach in User Defined Function (UDF) format:

Public Function WhereIs(rIn As Range, sIn As String) As String
    WhereIs = ""
    Dim r As Range
    For Each r In rIn
        If InStr(1, r.Text, sIn) > 0 Then
            WhereIs = r.Address(0, 0)
            Exit Function
        End If
    Next r
End Function

and here is an example of its use in the worksheet:

EDIT#2

It is possible to lookup a value in two dimensions without VBA
say we have data like:

In K1 enter:
hello

In K2 enter the array formula

=MIN(IF($A$1:$H$14=K1,COLUMN($A$1:$H$14)-COLUMN($A$1)+1))

In K3 enter the array formula

=MIN(IF($A$1:$H$14=K1,ROW($A$1:$H$14)-ROW($A$1)+1))

In K4 enter:

=ADDRESS(K3,K2)

K2 gives the column, K3 gives the row, and K4 gives the address of the cell ($E$11)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key.