Find function in Macro between different workbooks

**KaitMacN** · 08-30-2011, 02:41 PM

I am trying to use a macro that will look at a date that I enter and find that date adn corresponding data in a different workbook.

For simplicity sake I have included a sample of data in an attachment but in the same workbook for now!

On "Sample Calendar" tab I want to be able to change that date and have it search for that date to fill in the blanks for Breakfast, Lunch and Dinner for the three week section (in theory)

Any and all help is greatly appreciated!

**barryleajo** · 08-30-2011, 04:00 PM

Hi KaitMacN
Perhaps adapt the attached?
Note that I have extended your range of 'lookup' dates.
Run the macro called "fndate"
Barry

PS You will need to add some error checking for if the date isn't found

**KaitMacN** · 08-31-2011, 08:17 AM

Thats great! Thank you!!

**KaitMacN** · 08-31-2011, 08:28 AM

What would the code be so that the search looks in another workbook rather than another sheet? Do I just change what ws2 is?

**barryleajo** · 08-31-2011, 02:54 PM

Hi KaitMacN
Yes - exactly right.
Assuming the second workbook is called "Book2" with the same named worksheet it would look like:

Set ws2 = Workbooks("Book2").Worksheets("Sample data")

I believe both workbooks need to be open.
BTW thanks for the feedback.
Barry

**KaitMacN** · 09-02-2011, 09:39 AM

WHen I am trying to apply this macro into my actual workbook (even without linking two a second, literally cut and paste) it is giving me an error of:
runtime error '91':
object variable or with block variable not set

and the part of the code that is being higlighted is\

fndrow = ws2.Range("a4:A5000").Find(what:=dte, after:=ws2.Range("A4"), _
            LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlPrevious).Row

Any thoughts?
I have kept all the parameters the same between the documents