Return Address Of Cell Containing Specific Text String

    Hi everybody.


    I've browsed through several examples I've found here and even tried playing with a bit of code to solve this problem, but failed miserably. Any help that can be provided would be greatly appreciated.


    I've got a macro that reads a text file into a single column with range "A1:A10000" in worksheet "Source" that lives within the workbook containing this macro. I'd like to be able to search that worksheet for the cell that contains a particular text string, say " Excel", and have the macro return the address of the cell that contains it. I've tried using the .Find property but I only seem to be getting the value that I'm searching for instead of the cell address. Clues?


    Much appreciated.

    Re: Return Cell Address Of Cell Containing Specific Text String


    Is the string you are searching for going to be an exact match for a cell ?
    If so, try:

    Code
    =match("string",a:a,0)


    This will return the row number of the cell that matches the string you searched for.

    Re: Return Cell Address Of Cell Containing Specific Text String


    using Find something like this.


    [vba]Sub X()


    Dim rngX As Range

    Set rngX = Worksheets("Source").Range("A1:A10000").Find(" Excel", lookat:=xlPart)
    If Not rngX Is Nothing Then
    MsgBox "Found at " & rngX.Address
    End If

    End Sub[/vba]

    [h4]Cheers
    Andy    [/h4]

    Re: Return Cell Address Of Cell Containing Specific Text String


    A worksheet formula way
    =ADDRESS(MATCH(" Excel*",$A$1:$A$100,0),1)


    More on the Find Method here. Take note that some settings should be set each time.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login