On error resume next

[dugdugdug]

I am trying to split my data in Field 1 by the delimiter ^.

For most rows in my data, there are three ^ but some rows there is only one. (I found this out by adding the counter variable). When this happens, the code hangs.

I have put an on error but it still hangs.

Does anyone have any idea how I can overcome this?

Thanks

Please Login or Register  to view this content.

[Andy Pope]

You could either split the F1 content into a temporary variable and then check the UBOUND value before assigning values to F2..F4

or add extra delimiters to ensure split

Please Login or Register  to view this content.

[mike7952]

Give this a try on a copy of your db

Please Login or Register  to view this content.

[dugdugdug]

Give this a try on a copy of your db

Please Login or Register  to view this content.

Thanks but first problem is that F1 is not defined. Taking off Option Explicit, it said Item not found in this collection on this line:

Please Login or Register  to view this content.

[mike7952]

My bad try this I forgot the ""
.
x = Split(.Fields("F1").Value, "^")

[dugdugdug]

You could either split the F1 content into a temporary variable and then check the UBOUND value before assigning values to F2..F4

or add extra delimiters to ensure split

Please Login or Register  to view this content.

Andy's suggestion (adding more ^) works - thanks.

However, for some unknown reason, when running my original unaltered code, I get a new message:

Run-time error '3052':

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry.

What does this mean?

I can step and continue though, so should I just add an Application.DisplayAlerts = False to overcome this message?

[dugdugdug]

My bad try this I forgot the ""
.
x = Split(.Fields("F1").Value, "^")

Thanks but now I get: "Item not found in this collection."

Please Login or Register  to view this content.

[mike7952]

Dim indx as Long

[dugdugdug]

Dim indx as Long

SOLVED!

I had quite a few ^ so needed to alter the Table first (add more fields).

Thanks for your effort.

Any ideas about this?

"Run-time error '3052':

File sharing lock count exceeded. Increase MaxLocksPerFile registry entry."

[mike7952]

Try

Please Login or Register  to view this content.

[Andy Pope]

isn't the field reference via index zero based?

If you only have the fields F1..F4 then wouldn't they have the indexes 0..3

[mike7952]

Your right Andy, Im Just waking up. Should be +1

[dugdugdug]

Thanks (that wasn't an issue as I've remarked above).

I have a lot of data (hence using Access instead of Excel) and running your code was working (hadn't finished) when I appear to have spoken too soon!

It runs smoothly, then throws up a Run-time error '3001': Invalid argument

Please Login or Register  to view this content.

Why would it work for the first 500K rows, then suddenly hang?

2^7567952LN^-1.5%|Value^EUR^USD^606181.356800^^ ' THIS LINE WORKS
2^7567952LN^-1.5%|Delta^EUR^USD^-7505900.533108^^ ' HANGS

[mike7952]

Does all your lines have the ^^ at the end? If so try

Please Login or Register  to view this content.

[dugdugdug]

Does all your lines have the ^^ at the end? If so try

Please Login or Register  to view this content.

Most do but not all.

My suspicion is that there's something funny with my database.

I started from scratch and got the same problem as above, ie Invalid argument but on a different line of data.

Back to my original question, why won't On Error Resume Next skip over the errors?

[dugdugdug]

I suspect my problem lies with the actual database itself, in that when I run the code, it exceeds 2GB, hence throwing up an error.