Efficient Nested Loop Solution

[Jonas]

Hello,

I'm pursuing writing a program that runs quickly. I have written a
program with a for loop that works but it takes a very long time to
run. I let it run for two days and it still wasn't done running. The
program finds all combinations (not all combinations; just as many as
I want. If I want more, I just add another procedure with another
loop.) of numbers in a particular column and sums them along the way.
If the sum of them is equal to zero, the program copies and pastes the
rows associated with the cells that summed to zero.

For a unique identifier, I incremented the rows from the top to the
bottom. The info below will give you an idea of what I am trying to
achieve. I sum the cells in a particular column that are associated
with the unique identifier. Here is the start of how the code does
the combinations.

procedure 1
(1,2) (1,3) (1,4)..................

procedure 2
(1,2,3) (1,2,4) (1,2,5)........
(1,3,4) (1,3,5) (1,3,6)........
(2,3,4) (2,3,5).....
.......

procedure 3
(1,2,3,4) (1,2,3,5) (1,2,3,6)

Do you have any general suggestions for making this type of program
run quickly? Should I learn C++?

Your responses are appreciated.

 

[Kenneth Hobson]

Without seeing your vba code, it is hard to advise. A short snippet of the
copy paste routine would suffice.

Typically, screen updates and auto calculation slow opertions where copy and
pasting occurs. Copy and pasting is usually inefficient. For the first two
cases, I use these routines. Use SpeedOn at the start of your code and
SpeedOff at the end. I like to use On Error to make sure SpeedOff is
executed.

Sub SpeedOn()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
End Sub

Sub SpeedOff()
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub