Need Find in Open Workbook Code Debugged. HELP

[murphy00]

I am trying to do a find in another open workbook and return the results to the active workbook. I get an error in the Set c= line. Can anyone debug this?

Sub GetItNow()
Dim NextAvailableRow As String
Dim firstaddress As String
Dim fndName As String
Dim adr As String
Dim pLt As String
Dim pRt As String
Dim cLt As String
Dim cRt As String
Dim c As Object
Range("A41:D65535").ClearContents
NextAvailableRow = Range("A65536").End(xlUp).Offset(1, 0).Address
fndName = Range("A2").Value & "-*"
Set c = Workbook("Database").Worksheets("Circuit").Columns("A").Find(fndName, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
adr = c.Address
pLt = Range(NextAvailableRow).Address
pRt = Range(NextAvailableRow).Offset(0, 3).Address
cLt = Range(adr).Address
cRt = Range(adr).Offset(0, 3).Address
Worksheets("Circuit").Range(pLt, pRt).Value = Workbook("Database.xls").Worksheets("Circuit").Range(cLt, cRt).Value
NextAvailableRow = Range(NextAvailableRow).Offset(1, 0).Address
Set c = Workbook("Database").Worksheets("Circuit").Columns("A").FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End Sub

Thank you,
Murph

[Mark O'Brien]

What does the error say?

The only thing I can think of without knowing that is you might want to change the columns part of the "Set c" line to:

Columns("A:A")

Also, in your declarations, you can declare "c" as a "Range" instead of an object.

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-18 07:04 ]

[KniteMare]

First change all instances of workbook to workbooks.
Second Workbooks("Database").Worksheets("Circuit").Columns("A") must exist

I did not create dummies of your workbooks to test out the results hopefully the above spelling correction will get you started

Yours in EXCELent Frustration
KniteMare

[murphy00]

Knight Mare,

I used your changes and it worked beautifully.

Thank you for all of your help,
Murph

[KniteMare]

np
I've made that same error myself, Workbook instead of ...books.