Need Find in Open Workbook Code Debugged. HELP

murphy00

New Member
Joined
Feb 17, 2002
Messages
15
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?

Code:
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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.

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-04-18 07:04
 
Upvote 0
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
 
Upvote 0
Knight Mare,

I used your changes and it worked beautifully.

Thank you for all of your help,
Murph
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top