Find date in another workbook

S

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
 
M

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)
Click to expand...
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.
Click to expand...

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)
 
S

scain2004

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

Melanie Breden

Ok, I tried it exactly like that and it gave me a "Object Variable or
With block variable not set" error.
Click to expand...

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)
 
S

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!
 
Top