overcoming maximum number of iterations 1

[nevertoolate]

I have a spreadsheet containing thousands of lines of data that I search to extract data for reports. A current sheet has 43,000 lines, and the vba program crashes after searching 32767 rows. The search is done in a "Do-Loop While" loop, and 32767 is the maximum number of iterations possible in excel. So I guess that 32767 is also the maximum number of loops allowed in excel. The error message is an "overflow" error.

I have tried adding another loop which is initiated if and when 32767 records have been searched in the first loop, but vba seems to remember this and the same overflow error is returned when the new loop is first initiated.

I find this especially irritating since I can refer to cell(32768,1) in the program and get its value, but I cannot refer to cell(32767+NewCounter,1), where NewCounter =1, without getting an overflow error.

Any ideas?

 

[bryanbayfield]

How have you declared the variable which you're using as a loop counter?

My guess is that you've declared it as "Integer" which only tolerates whole values in the range [-32,627, 32,627], which is why you're getting overflow errors.

Try declaring the variable as a "Long" type and you should find the problem corrects itself.

Bryan.

 

[nevertoolate]

I had my doubts, Bryan, since my app was crashing at 32767, not 32627, but I tried it anyway. And you were RIGHT! Such a simple solution, too.

Many, many thanks...

 

[bryanbayfield]

Ah - I got the range for an integer wrong - it should be -32,768 to 32,767, which explains the anomaly.

Apologies for teh confusion.

Bryan.