"Runtime error '-2147417848 (80010108)' Method 'find' of object 'Range' failed"

Hello,

I have written the following sub which is called from another "main" sub -

Please Login or Register  to view this content.

As you can see, Check_Cals() keeps on running till the phrase "#N/A Requesting Data..." is not found in the defined range 'unionrange1'. Once that happens, it does some calculations, saves and closes the opened file 'Myfile.xlsm' and exits the sub. However, when I run this loop, I'm getting this error for the line -

Please Login or Register  to view this content.

error - "Runtime error '-2147417848 (80010108)' Method 'find' of object 'Range' failed".
Could anyone please guide me why this is happening? And suggest how I can remove this bug?

Thanks,
excelworker_1

Hi TMS,

Thanks for the prompt reply. You are right - it doesn't loop through. It runs till the end in the 1st pass and then gets stuck at the 2nd pass. My understanding is it goes into the 'else' loop during 1st pass, assigns p to Nothing and cthen alls "Check_Cals" for the 1st time. When it starts passing through the sub for 2nd pass, something is going wrong there (when p is re-assigned to p = unionrange1.find(....)).

My aim is the code should run till "#N/A Requesting Data" is not found. For each cell in the range that I've defined in the code, data is being pulled from Bloomberg. And I don't think there is any residual data that is giving the error. Because I checked that independently and that works fine.

Please guide me accordingly.

Thanks a lot,
excelworker_1

Hello romperstomper,

Maybe a silly question by me, but if you look at my code in the first post, I want to do the calculations, saving, calling of another sub etc. only once when p finally becomes Nothing. I think what you have written is 'do the calculations till p becomes nothing'. Can you suggest how to change that to what I'm aiming at doing?

Also can you explain what "Set p = unionrange1.FindNext(p)" does exactly?

Thanks a lot,
excelworker_1

I tried what you suggested, and it is not working. This is how I re-oriented the code -

Set p = unionrange1.Find("#N/A Requesting Data...", LookIn:=xlValues)

Do Until p Is Nothing

' Does some calculations here

' Calls another sub
Call Transfer_To_Main_Sheet

'saves and closes the file Myfile.xlsm
Workbooks(MyFile & ".xlsm").Save
Workbooks(MyFile & ".xlsm").Close

Set p = unionrange1.FindNext(p)
Loop

but it gives a "Runtime error 424 Object required" error. Please guide me.

Thanks,
excelworker_1

Oh ok ok. Now I got what you meant. Will try right away and confirm in a bit if it is working. It is taking a bit of time for Excel to pull the external data.

Hello romperstomper,

I tried what you said, but it does not work. I guess it goes in a perpetual loop because ultimately the excel file(s) get stuck and I have to force-shutdown everything and restart again.

Please guide me.

Thanks,
excelworker_1

I did the exact same thing you suggested. Here is the code -

Please Login or Register  to view this content.

I also checked that there is not problem with the stuff after "Loop". The code gets stuck when it goes into the "do until" loop. It doesn't give any error message, but just gets stuck and I have to re-start excel. I also tried changing my range to a smaller scope (just 2 rows) in case it is an issue about 'lot of time required for pulling data'. But even for that, it doesnt work.

Thanks,
excelworker_1

Just above "Set p = unionrange1.Find("#N/A Requesting Data...", LookIn:=xlValues)" I have -

Please Login or Register  to view this content.

Basically, just defining the range "unionrange1".

Still not working :-(
It still goes into a perpetual loop. Btw just realized, shouldn't n be initialized to something? I tried both with and without that...