.find using variables

[danmack]

Hi all,

I'm trying to run a macro that searches for a value in another workbook (wbk2) which could appear multiple times in different worksheets. Once found i want to copy the row the value is found on and paste to the source workbook (wbk1). I have split the code into modules. The first works fine, it lists all files in the source folder, the second opens the files one by one (loops through) and calls the search macro for each. The search macro is below.

Please Login or Register  to view this content.

I can't get past the

Please Login or Register  to view this content.

part. I get the object variable with block variable not set error. I've tried to wrap my brain round it and am failing, could any of you good people help explain this to me?

Thanks

Dan

[:) Sixthsense :)]

Because Ws is blank without holding any SHEET

[danmack]

Ahh, thanks for your help. So do i need to do something like

Please Login or Register  to view this content.

bearing in mind i want to search the whole workbook? Any further advice would be much appreciated.

[:) Sixthsense :)]

You can't say sheets in one shot... Instead you have to loop through each sheet of that workbook like this...

Please Login or Register  to view this content.

[danmack]

Brilliant, i will try this and let you know how i get on. Thanks for your patience with this self taught, intellectually limited and reluctant coder!

[:) Sixthsense :)]

But you need another loop may be a Do or Do while or Do Until loop to find all the matches before moving to the next sheet..

Please Login or Register  to view this content.

[danmack]

Thanks a lot for your help, i'm getting a 'Do without Loop' error now but have learnt something new! I will do my best to work this out myself and report back next week with code if necessary.