Most efficient way of looping through a table

[vanian]

I created a VBA routine, which works well, that loops through a table and will calculate costs associated to a client. However, with a lot of data point, the routine can be slow (perhaps 10 - 15 min depending on the data). I am wondering if there is a more efficient way to do this?

The routine will evaluate each cell in a range (i.e. in the example at the end of this text, assuming Product A is on line 10, the routine will loop in range D10:E12) and if there is a value in that cell, will trigger a calculation (for example, if the cell currently evaluated is D10, the calculation would be 200 / 600 * $10 000).

The range can be quite huge and there is not always a lot of data in the range. What would be the most efficient way to loop through the range?

One possible way to accelerate the routine would be to simulate the "end" and "right arrow" to select the next available data point (assuming the 'looping' is from column D and E of line 10, then column D and E of line 11, and so on).

Apart from this, is there a more efficient way of coding the routine?

PS - the actual routing is much more complex than this example, it's actually a three level for..next loop that will scan ranges in three different worksheets and store the results of the calculation in another worksheet.



A....................B...................C........................... D.............. E
.......................COST..........TOTAL UNITS .....Client 1.....Client 2
Product A .....$10,000........... 600................. 200........... 400
Product B .....$25,000...........100..................100
Product C..... $75,000...........800..................................... 800

 

[brettdj]

Use a variant array for the looping, not a range loop

1) write your range(s) to an array
2) perform you calculation in the array
3) dump the array back to the range(s)

My article here which has a sample to remove leading zeros from cell values should be useful,

Cheers

Dave

 

[vanian]

Hello Dave,

Thanks for the reply... very interesting technique, I can see how it can be very useful.

However, I can't use it as is - the matrix (range) can't be modified as it contains end-user input and we do not want to change this data. I also can't perform the calculation in an array, because the ultimate calculation is based on the results of three different arrays. In a nutshell the routine goes like this :

for ARRAY1 = beginning to end
....for ARRAY2 = beginning to end
........for ARRAY3 = beginning to end
............Calculation
............Write results of calculation along with specific data from
............ARRAY1, ARRAY 2, and ARRAY 3 to a new worksheet :
............each line in the new worksheet contains the complete detail of
........... that data point, from it's beginning on ARRAY 1 (which contains
........... costs from the general ledger) up to it's final path in ARRAY 3
........... (which may be a client, a product line, whatever we want).
........next ARRAY3
....next ARRAY 2
next ARRAY 1

It looks like I could use the variant array technique for the looping purposes (scan which cell in the array contains data) and write the complete audit trail from ARRAY1 to ARRAY 3, along with the calculation, in the new worksheet.

It's certainly worth trying, it probably won't require much change in my code.

Best regards,

Stephane

Use a variant array for the looping, not a range loop

1) write your range(s) to an array
2) perform you calculation in the array
3) dump the array back to the range(s)

My article here which has a sample to remove leading zeros from cell values should be useful,

Cheers

Dave

 

[Ruddles]

...is there a more efficient way of coding the routine?

Perhaps if we could see the actual code we'd be in a better position to comment.

(Make sure you post it inside CODE tags - the # icon in the advanced editor toolbar.)