Find address of the first occurrence of a specific word on a different sheet

[mlarson]

Hi all,

I am hoping somebody can help me with this. I saw some threads on a similar problem but they seemed to be for finding numeric values, and usually on the same sheet. Anyway, with that said, here is what I'm trying to do...

I'm trying to find the address/cell location of the first occurrence of a specific word on a different sheet. Note: By "first" I mean by row, not column. So, an appearance in row 1, even if it's in column ZZZ, is still "first" over an appearance in row 3 column A.

So, I have two sheets, we'll call them Sheet1 and Sheet2
On Sheet1, in cell B4 it says "alphabet"
I would like a formula for cell B5 that gives the cell location of where "alphabet" first appears on Sheet2. (note: case-sensitive doesn't matter)

So, let's say on Sheet2 the contents of cells are as follows:
A1 reads "I went to the zoo."
B6 reads "Kids don't always like to do math"
Z4 reads "The alphabet is important for kids to learn."
Y3 reads "Recreation is good for the health of children"
G19 reads "English teachers usually like the alphabet more than they like multiplication tables."

Given the above, the output/return would be Z4, because that is where "alphabet" FIRST appears, even though it also appears in G19. In other words, cell B5 on Sheet1 should show the output "Z4" (or $Z$4 is fine, too).

I hope I explained this clearly! Thanks in advance for helping!

 

[mlarson]

By the way, if there is some kind of multi-step process to achieve this end goal, I'm fine going that route as well. It doesn't have to be one VBA, it could be a few different steps, or if I need to use some kind of archaic way of doing it. Anyway, any help you can provide is appreciated, thank you!

 

[Sufiyan97]

See if this helps

Book4
	A	B	C	D	E	F
1
2
3
4		Alphabet		The alphabet is important for kids to learn.	$D$4
5
6
7
Sheet1

Cell Formulas
Range	Formula
E4	E4	=ADDRESS(MATCH(INDEX($D$4:$D$13,AGGREGATE(15,6,ROW($D$4:$D$13)-ROW($D$4)+1/ISNUMBER(SEARCH(B4,$D$4:$D$13)),1)),$D$1:$D$13,0),MATCH(INDEX($D$4:$D$13,AGGREGATE(15,6,ROW($D$4:$D$13)-ROW($D$4)+1/ISNUMBER(SEARCH(B4,$D$4:$D$13)),1)),$D$1:$D$13,0))

 

[James006]

Hi,

You could test an UDF

Function FindmyString(SearchString As String, SearchRange As Range) As String
' Input in cell B5  =FindmyString("alphabet",a1:z50)
Application.Volatile
Dim c As Range
        Set c = ActiveSheet.Cells.Find(What:=SearchString, _
            After:=Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not c Is Nothing Then
            FindmyString = c.Address(0, 0)
        Else
            FindmyString = " Not Found ... "
        End If
End Function

 

[James006]

Just noticed you had asked to find the string in another worksheet ....

Below modified UDF

Function FindmyString(SearchString As String, SearchRange As Range) As String
' Input in cell B5  =FindmyString("alphabet",a1:z50)
Application.Volatile
Dim c As Range
Dim wksh As Worksheet
    For Each wksh In ActiveWorkbook.Worksheets
        MsgBox wksh.Name
        Set c = wksh.Cells.Find(What:=SearchString, _
            After:=wksh.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not c Is Nothing Then
            FindmyString = wksh.Name & "!" & c.Address(0, 0)
            Exit Function
        Else
            FindmyString = " Not Found ... "
        End If
    Next wksh
End Function

 

[mlarson]

Just noticed you had asked to find the string in another worksheet ....

Below modified UDF

Function FindmyString(SearchString As String, SearchRange As Range) As String
' Input in cell B5  =FindmyString("alphabet",a1:z50)
Application.Volatile
Dim c As Range
Dim wksh As Worksheet
    For Each wksh In ActiveWorkbook.Worksheets
        MsgBox wksh.Name
        Set c = wksh.Cells.Find(What:=SearchString, _
            After:=wksh.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not c Is Nothing Then
            FindmyString = wksh.Name & "!" & c.Address(0, 0)
            Exit Function
        Else
            FindmyString = " Not Found ... "
        End If
    Next wksh
End Function

James006, I think we're in business! Only question I have is that if the workbook has, say, 30 sheets and I only want it to check Sheet2, how would that change the UDF? Thanks again!

 

[James006]

Hi,

Below the modified version

Function FindmyString(SearchString As String, SearchRange As Range) As String
' Input in cell B5  =FindmyString("alphabet",a1:z50)
' Modified Version
Application.Volatile
Dim c As Range
        ' Adjusted for Sheet2
        Set c = Sheet2.Cells.Find(What:=SearchString, _
            After:=wksh.Cells(1, 1), _
            LookIn:=xlValues, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not c Is Nothing Then
            FindmyString = c.Address(0, 0)
            Exit Function
        Else
            FindmyString = " Not Found ... "
        End If
End Function

 

[James006]

Oops ... skipped a delete for each wksh ...
After:=wksh.Cells(1, 1), _
shoul now read :
After:=Cells(1, 1), _

Hope this will help

 

[mlarson]

Oops ... skipped a delete for each wksh ...
After:=wksh.Cells(1, 1), _
shoul now read :
After:=Cells(1, 1), _

Hope this will help

Thank you, thank you, thank you! Worked perfectly.

 

[James006]

You are welcome