Automate the Find function in Excel

[Eliott]

Hi, I cannot find a way to do this:

I have a cell (Y8) with content. In this case the content is a
variable...the value can change. I want to create a maco to search/find the
next occurrence of the contents of Y8 on the same workseet only. I created
the macro below where I copied the content of Y8 using the find function
(Edit-->Find), but the macro does not work as I imagined. The copied data
remains static. It does not change from the orginal content of Y8...in this
case "04/19/08". I would greatly appreciate help in finding a way to perform
this task. Here's the macro I created:

Sub go_to_date()
'
' go_to_date Macro
' Macro recorded 4/18/2008 by espencer
'

'
Range("Y8").Select
Application.CutCopyMode = False
Selection.Copy
Cells.Find(What:="04/19/08", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

 

[Don Guillett]

try this. However, it is rarely necessary/desirable to SELECT the cell to
work with it.

Sub gtd()
mc = "y"
what = Cells(8, mc).Value
Cells.Find(what, After:=Cells(8, mc), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext).Activate
End Sub

 

[J Sedoff]

This will find the next cell that contains the same value as what is in Y8
then replaces it with a formula linking it to Y8, so that as Y8 changes, so
too will this cell.

Is this what you wanted?

Dim myCell, nextCell As Range
Set myCell = Range("Y8")
Set nextCell = Cells.Find(What:=myCell.Value, After:=myCell,
LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False _
, SearchFormat:=False)
nextCell.Formula = "=" & myCell.Address


Hope this helps, Jim