Error 3021: Either EoF or BoF is True.

Hi All,

Everytime I try to adapt this code that I received lots of help with from DonkeyOte and Andy Pope, to a new workbook, I seem to run into new problems.

This time I am getting a runtime error: 3021 Either BoF or EoF is True.....

This is the code I have:

Please Login or Register  to view this content.

I have PART_IDs listed in my spreadsheet, starting at row 8 and column 4 (i.e. D8)

the Debug.Print LR statement correctly returns 172 as last row. There are some blank rows in between, but those should be skipped.

The code is supposed to go through each Part ID in column D and extract the Qty breaks and Unit prices from a table called MARKET_PRICE in my ERP database

Thanks in Advance

Hi, that gives me an error

Compile Error: Method or Data Member not Found

and highlights the .MoveFirst line just after the EndIf.

Note: In previous spreadsheets It worked as I originally had formatted.

I only changed the locations (i.e. now using Column D, starting at row 8) and I changed the fields and table information from my database to use.

Also note, my original error 3021 also highlighted the .MoveFirst line when I clicked Debug.

Have you got the reference set to the ActiveX Data Objects library? Also, have you checked that strIDS1 contains the values you think it should?

Hi romperstomper,

I did check the ActiveX library is set ... and yes it is.

I think I figured out the problem, and your second statement seems to confirm it.

I changed the first part in D8 to a part I know has values in the MARKET_PRICE table, and reran the macro. It did not error out.

It however only returned data for this first part.

I just added the Debug.print strIDs1 statement.

And received only 2 parts in the string, the first 2 parts in column D. The first is okay, but the 2nd is not.. it doesn't have data in the MARKET_PRICE table. The macros seemed to just stop there.

Is there a way to ensure it continue on whether it finds data in the MARKET_PRICE table or not, until the end of my list and only pull data that exists?

I added the .MoveNext

Doesn't seem to change anything.

Still not getting results past first record.

As far as I can see, all you were missing was a check to make sure there were any records in the recordset to start with:

Please Login or Register  to view this content.

I added that, and it didn't make a difference either.

If the first Part exists in the table, it pulls the data and stops.

If the first Part doesn't exist, it pulls no data

It doesn't seem to want to go past a blank cell. e.g. if I put the same part in D8 and D9, I get results in both rows, but doesn't continue since next row doesn't exist in table, but if D9 is blank, only first row results, then it stops.

So if you have data in D9 but not D8, you get nothing back at all (even if D9 is valid)?

In that case it does return values in for row 9 as expected, but again it seems to stop there.

I have the same part listed a few more rows down, and I get nothing in that row.

This is what I get in the Immediate window:

'14165','14165','30289'
172
'14165','','30289'
172
'','14165'

The 172 is my debug.print LR

The first string is when I have same part in D8 and D9, D10 part doesn't exist in MARKET_PRICE table, but is a valid Database Part.
The second string is when D8 has value, blank in D9 and D10 has same non-existant part
The third string is when blank D8, valid part in D9... this time it does not show the 30289 invalid part.

Curious. What if you use Filter instead of Find:

Please Login or Register  to view this content.

Exactly the same results.

What are the values of FR, LR and strIDs1? Going to have to step through this, I think.

In another file where I look for a different field in a different table, I use this very similar code and it works just fine... even with blanks:

Please Login or Register  to view this content.

Not sure if that offers any clues??

Attached is a sample of what I am working with:

Does that answer your questions?

Just so I know exactly where we are, can you post the exact code you are now using, together with the values for FR, LR and strIDs1 and any errors that occur. It would also be helpful if you could step through the code so that you can see what if anything is happening when the repeated value is reached.
I do have one concern that you have some unqualifed range references in there (in both codes actually). For example:

Please Login or Register  to view this content.

does not specify a worksheet, and this bit is worse as it only specifies a sheet for part:

Please Login or Register  to view this content.

That should be:

Please Login or Register  to view this content.

but I don't think they are the issue here.

Can you try this version please?

Please Login or Register  to view this content.

This is the exact code:

Please Login or Register  to view this content.

And with the attachment above, these are the I.W. results

8
172
'14165','','30289'

Note:

If I remark the .MoveNext and use .MoveFirst, I get the 3021 error, otherwise no errors, just first line results.

Originally Posted by romperstomper

Can you try this version please?

It gives no results at all and I.W. shows a zero (0)

Forgot to move the debug statement! I've added some additional debugs here - can you tell me what the output is:

Please Login or Register  to view this content.

Hi, I get the following:

LR = 172
rngIDs address: $D$8:$D$172
ID list: '14165','','30289','','35118','','','38185','38186','39232','53022','143521','37178','37177','33582','53027','10039','17573','14165','4798','4797','6176','6462','52606','29648','004BU','1009','1687','2067','1003','23165','23101','23164','14301','38856','','29439','29440','22885','3874','22884','','29444','53199','','','','','35123','','29448','','33958','29447','23142','10282','10271','10272','10273','10275','1579','1577','1605','10280','1580','1578','1606','10281','53033','','6520','6591','6590','6521','6597','5275','10276','33126','49662','14340','16769','44804','3442','2385','3666','5240','3443','2386','3668','5241','3444','2387','3667','5242','7330','7328','7327','7325','','','','','','10269','30270','10283','','22886','','1075','','005BU','006BU','008BU','010BU','012BU','012BU','114BU','L116B','155BU','L240B','024BU','024EP','024VT','53447','139BU','139EP','139VT','53448','234BU','234EP','234VT','5094','70085','75007','114BU','114EP','114VT','115BU','115EP','115VT','32489','118BU','118EP','118V
T','32488','120BU','120EP','120VT','32490','','2381','2685','','2005','2686','10039','1570','3879','1057','1066','1070','1077','1085','1091'

still no result in the spreadsheet...

OK, let's get REALLY verbose

Please Login or Register  to view this content.

Getting this:

LR = 172
rngIDs address: $D$8:$D$172
ID list: '14165','','30289','','35118','','','38185','38186','39232','53022','143521','37178','37177','33582','53027','10039','17573','14165','4798','4797','6176','6462','52606','29648','004BU','1009','1687','2067','1003','23165','23101','23164','14301','38856','','29439','29440','22885','3874','22884','','29444','53199','','','','','35123','','29448','','33958','29447','23142','10282','10271','10272','10273','10275','1579','1577','1605','10280','1580','1578','1606','10281','53033','','6520','6591','6590','6521','6597','5275','10276','33126','49662','14340','16769','44804','3442','2385','3666','5240','3443','2386','3668','5241','3444','2387','3667','5242','7330','7328','7327','7325','','','','','','10269','30270','10283','','22886','','1075','','005BU','006BU','008BU','010BU','012BU','012BU','114BU','L116B','155BU','L240B','024BU','024EP','024VT','53447','139BU','139EP','139VT','53448','234BU','234EP','234VT','5094','70085','75007','114BU','114EP','114VT','115BU','115EP','115VT','32489','118BU','118EP','118V
T','32488','120BU','120EP','120VT','32490','','2381','2685','','2005','2686','10039','1570','3879','1057','1066','1070','1077','1085','1091'
14165 found OK
30289 not found
35118 not found
38185 not found
38186 not found
39232 not found
53022 not found
143521 found OK
37178 found OK
37177 found OK
33582 found OK
53027 not found
10039 found OK
17573 found OK
14165 found OK
4798 found OK
4797 found OK
6176 found OK
6462 found OK
52606 found OK
29648 found OK
004BU found OK
1009 found OK
1687 found OK
2067 found OK
1003 found OK
23165 found OK
23101 found OK
23164 found OK
14301 found OK
38856 not found
29439 not found
29440 not found
22885 found OK
3874 found OK
22884 found OK
29444 not found
53199 not found
35123 not found
29448 not found
33958 not found
29447 not found
23142 found OK
10282 found OK
10271 found OK
10272 found OK
10273 found OK
10275 found OK
1579 found OK
1577 found OK
1605 found OK
10280 found OK
1580 found OK
1578 found OK
1606 found OK
10281 found OK
53033 found OK
6520 found OK
6591 found OK
6590 found OK
6521 found OK
6597 found OK
5275 found OK
10276 found OK
33126 found OK
49662 found OK
14340 found OK
16769 found OK
44804 found OK
3442 found OK
2385 found OK
3666 found OK
5240 found OK
3443 found OK
2386 found OK
3668 found OK
5241 found OK
3444 found OK
2387 found OK
3667 found OK
5242 found OK
7330 found OK
7328 found OK
7327 found OK
7325 found OK
10269 found OK
30270 found OK
10283 found OK
22886 found OK
1075 found OK
005BU found OK
006BU found OK
008BU found OK
010BU found OK
012BU found OK
012BU found OK
114BU found OK
L116B found OK
155BU not found
L240B not found
024BU not found
024EP not found
024VT not found
53447 not found
139BU found OK
139EP found OK
139VT found OK
53448 not found
234BU not found
234EP not found
234VT not found
5094 not found
70085 not found
75007 not found
114BU found OK
114EP found OK
114VT found OK
115BU found OK
115EP found OK
115VT found OK
32489 found OK
118BU found OK
118EP found OK
118VT found OK
32488 found OK
120BU found OK
120EP found OK
120VT found OK
32490 found OK
2381 found OK
2685 found OK
2005 found OK
2686 found OK
10039 found OK
1570 found OK
3879 found OK
1057 found OK
1066 found OK
1070 found OK
1077 not found
1085 not found
1091 not found

Then are you sure there is actually data in the columns you are retrieving from the database? (and you are looking in columns N:Q right? )

That last code gave those results in the Immediate Window but nothing in the spreadsheets (yes.. looking in N:Q)

But, I just reran the previous code you gave, and it now shows the proper results

Is there a difference besides the extra debug.prints?

Nope other than the check to skip empty cells in the For Each c loop.
Are you sure the data actually was in the database you are looking at before? (sounds almost as though the data hadn't been committed before.)

I haven't touched the actual database and I know the data was there because I had loaded it all in a few weeks ago.

I tried to rerun both codes again, and still only your first gives results...

... but that's okay. At least we got there in the end.

Thanks so much for your help and persistence, romperstomper.

Any time! At least we got there in the end...

Thanks.

Can ask a little extra bit?

I had to actually combine 2 columns of Parts to get 1 column...

I used a formula that returns a null if both columns are blank...

My Immediate Window shows the blanks cells as single apostrophes with a space between... so I think it is looking for a null part Id in my database.

How can I convert those back into true nulls, in the code?

You would have to loop through each cell in the range, check if it's value has a length greater than 0 and, if so, add it to the criteria string. Something like this:

Please Login or Register  to view this content.

which you would use with:

Please Login or Register  to view this content.

for example.

Ok thanks again. I will try to figure out how to incorporate that.