Using Find in a macro
 

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

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

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