Search a value in a column-B and return the values from column-A

[i200yrs]

Hello Excel Experts...need some help...see table below:
A B
1 Apple
2 Ball
3 Apple
4 Cat
5 Dog
I want an excel macro like if the user key-in "Apple" and click search will return values from column-A and list it in Column-C.
Like below result:
C
1
3

Hoping for usual supports...thanks

 

[Yongle]

code below assumes that Sheet2 cell D4 contains a value when the code is run

Sub FindText()
    Dim LastCel As Range, Found As Range, Addr As String
    Set LastCel = Sheets("Sheet2").Cells(Rows.Count, "D")
    Range(LastCel.Offset(5 - LastCel.Row), LastCel).ClearContents
    Set Found = Sheets("Sheet3").Range("B2:Z500").Find(Sheets("Sheet1").Range("A1").text, LookIn:=xlValues)
    If Not Found Is Nothing Then
        Addr = Found.Address
        Do
            LastCel.End(xlUp).Offset(1) = Found.Offset(, -Found.Column + 1)
            Set Found = Sheets("Sheet3").Range("B2:Z500").FindNext(Found)
        Loop While Not Found.Address = Addr
    End If
End Sub

 

[i200yrs]

code below assumes that Sheet2 cell D4 contains a value when the code is run

Sub FindText()
    Dim LastCel As Range, Found As Range, Addr As String
    Set LastCel = Sheets("Sheet2").Cells(Rows.Count, "D")
    Range(LastCel.Offset(5 - LastCel.Row), LastCel).ClearContents
    Set Found = Sheets("Sheet3").Range("B2:Z500").Find(Sheets("Sheet1").Range("A1").text, LookIn:=xlValues)
    If Not Found Is Nothing Then
        Addr = Found.Address
        Do
            LastCel.End(xlUp).Offset(1) = Found.Offset(, -Found.Column + 1)
            Set Found = Sheets("Sheet3").Range("B2:Z500").FindNext(Found)
        Loop While Not Found.Address = Addr
    End If
End Sub

THIS ONE IS PERFECT! THANKS A LOT TO YOU YONGLE ")

 

[Yongle]

thanks for your feedback