Using Find in a macro

[Phil Osman]

I have the following:
Cells.Select
Selection.Find(What:="1/07/2005", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("H95").Select

But, instead of the date that it is finding I want it to search for a date
that I previously copied in the macro. So literally:
Selection.Find(What:=Paste......

Can this be done somehow?

--
http://www.redbrick.dcu.ie/~pele

[Rowan]

Use a variable to hold the date you want to find. Assign the value to the
variable at the same time that you do the copy and paste. So if the variable
is myDate you can use:

Selection.Find(What:=myDate,...

Also you might want to change the code to set a range variable to the found
cell which allows you to check that something was actually been found before
selecting it. This will prevent an error occuring if Excel doesn't find the
date you are searching for:

Dim myRange As Range
With Cells
Set myRange = .Find(myDate)
End With
If myRange Is Nothing Then
MsgBox "Not found"
Else
' do whatever to myRange eg select
myRange.Select
End If

Hope this helps
Rowan

"Phil Osman" wrote:

> I have the following:
> Cells.Select
> Selection.Find(What:="1/07/2005", After:=ActiveCell,
> LookIn:=xlFormulas, _
> LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> MatchCase:=False, SearchFormat:=False).Activate
> Range("H95").Select
>
> But, instead of the date that it is finding I want it to search for a date
> that I previously copied in the macro. So literally:
> Selection.Find(What:=Paste......
>
> Can this be done somehow?
>
> --
> http://www.redbrick.dcu.ie/~pele

[Phil Osman]

I don't understand the first bit. I named the cell that holds the Date as
"Month".
In my macro I then have:
Selection.Find(What:=Month,

but it doesn't work ?

--
http://www.redbrick.dcu.ie/~pele


"Rowan" wrote:

> Use a variable to hold the date you want to find. Assign the value to the
> variable at the same time that you do the copy and paste. So if the variable
> is myDate you can use:
>
> Selection.Find(What:=myDate,...
>
> Also you might want to change the code to set a range variable to the found
> cell which allows you to check that something was actually been found before
> selecting it. This will prevent an error occuring if Excel doesn't find the
> date you are searching for:
>
> Dim myRange As Range
> With Cells
> Set myRange = .Find(myDate)
> End With
> If myRange Is Nothing Then
> MsgBox "Not found"
> Else
> ' do whatever to myRange eg select
> myRange.Select
> End If
>
> Hope this helps
> Rowan
>
> "Phil Osman" wrote:
>
> > I have the following:
> > Cells.Select
> > Selection.Find(What:="1/07/2005", After:=ActiveCell,
> > LookIn:=xlFormulas, _
> > LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
> > MatchCase:=False, SearchFormat:=False).Activate
> > Range("H95").Select
> >
> > But, instead of the date that it is finding I want it to search for a date
> > that I previously copied in the macro. So literally:
> > Selection.Find(What:=Paste......
> >
> > Can this be done somehow?
> >
> > --
> > http://www.redbrick.dcu.ie/~pele