Find date in another workbook

[scain2004]

I need to find a date in Production Calendar.xls from Production Jo
List.xls. So far this is what I've got:

Workbooks("Productio
Calendar.xls").Worksheets("Phoenix").Range("A:G").Cells
.Find(What:="9/24/2004", LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext
MatchCase:=False).Activate

message = CStr(ActiveCell.Location)



On the find statement I get an "Invalid or unqualified reference
error.

Actually, in the calendar itself the date is in format "24-Sep", no
sure if that matters or not since it's a date.

Any suggestions?

Thanks

 

[Melanie Breden]

I need to find a date in Production Calendar.xls from Production Job

List.xls. So far this is what I've got:

Workbooks("Production
Calendar.xls").Worksheets("Phoenix").Range("A:G").Cells
Find(What:="9/24/2004", LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

message = CStr(ActiveCell.Location)

On the find statement I get an "Invalid or unqualified reference"
error.

Actually, in the calendar itself the date is in format "24-Sep", not
sure if that matters or not since it's a date.

try this:

Sub FindDate()
Dim rngDate As Range

On Error Resume Next
Set rngDate = Workbooks("Production Calendar.xls").Worksheets("Phoenix") _
.Range("A:G").Cells.Find(#9/24/2004#)

If Not rngDate Is Nothing Then
MsgBox rngDate.Address
Else
MsgBox "Date not found"
End If
End Sub

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[scain2004]

Ok, I tried it exactly like that and it gave me a "Object Variable o
With block variable not set" error

 

[Melanie Breden]

Ok, I tried it exactly like that and it gave me a "Object Variable or

With block variable not set" error.

This error message cannot take place with the code, because
"On Error Resume Next" is set and the IF instruction is closed.

Did you examine whether is correctly written the file and sheet name?
Are the date values real, or generate by formulas ?

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[scain2004]

it didn't stop the code, but when I stepped through it, it showed the
error.

Anyways, I did figure it out and now it's working how I need it to with
some proper formatting.


Thank you for your help!