search and return cell address

F

Fan924

I need a macro to search a column of 20 cells. When it find the number
I am looking for, I need it to return the cell address it is found in.
Thanks
 
D

Don Guillett

Good time to learn. Turn on the macro recorder>select the
range>EDIT>find>>>>>
clean up the macro. Post back with additional needs.
 
D

Don Guillett

Since you made the effort to do it yourself, you may like this.

Option Explicit
Sub Findnum()
Dim mc As String
Dim myfind As Range
Dim mynum As Long
mc = "a"
mynum = InputBox("Enter number desired")
MsgBox mynum
Set myfind = Columns(mc).Find(What:=mynum, _
After:=Cells(1, mc), LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
If Not myfind Is Nothing Then Range("a1") = myfind
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
[email protected]
How do I find the address of the cell it is found in?
Click to expand...

Thanks, I found what I need
Range("A1").Value = ActiveCell.Address
 
Top