Hey, guys.

I recently wrote a procedure to get the sum of values in one column
based on multiple conditions for several other columns.

It first finds the first cell in the first column that matches the
first condition and, with offset method, compares the rest of the cells
in the row with the other conditions. If all match, it adds the value in
the last column to a variable. If it doesn't, it moves on to the next
matching row. And it loops until there's no more cell in the first
column that match the first condition.

The whole process is done in a user-defined function.

Hmm.... it works, all right, but the problem is that it takes too~
long.(Maybe longer than array formulas that I tried to walk away from)
My source data sheet has about 40,000 records and it's growing longer
each month by 2,000 records. I tested the code with the actual
datasheet at work, and guess what. It took more than 3 hours(it took
about 23 seconds per one query). Thanks to my clumsy coding technique,
I goofed around the whole afternoon.(doing other very important things
)
I didn't want to stop all the fun with Ctrl + Break. ;)

Any ideas to improve that?

Oh, I'll paste the code later.

Thanks.

Luke.


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=384804

Hard to say what to improve without seeing the code, but one of the biggest
"time-killers" in routines can be loops within loops (for example, if you are
already looping to check 40,000 lines and you have a loop inside that one
that checks another 40,000 lines, you now need to perform 40,000 * 40,000
operations).

I think, though, that you may need to reconsider using array formulas, since
as built-in formulas they are already more efficient.

"staying" wrote:


Hey, guys.

I recently wrote a procedure to get the sum of values in one column
based on multiple conditions for several other columns.

It first finds the first cell in the first column that matches the
first condition and, with offset method, compares the rest of the cells
in the row with the other conditions. If all match, it adds the value in
the last column to a variable. If it doesn't, it moves on to the next
matching row. And it loops until there's no more cell in the first
column that match the first condition.

The whole process is done in a user-defined function.

Hmm.... it works, all right, but the problem is that it takes too~
long.(Maybe longer than array formulas that I tried to walk away from)
My source data sheet has about 40,000 records and it's growing longer
each month by 2,000 records. I tested the code with the actual
datasheet at work, and guess what. It took more than 3 hours(it took
about 23 seconds per one query). Thanks to my clumsy coding technique,
I goofed around the whole afternoon.(doing other very important things
)
I didn't want to stop all the fun with Ctrl + Break. ;)

Any ideas to improve that?

Oh, I'll paste the code later.

Thanks.

Luke.


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=384804

try

Code
-------------------

Sub Analysis_2()
Dim cond, rng_reference As Range, i As Integer
Dim criteriarng As Range
cond = Array("Delphi", "200505", "NA", "AAA", "BBB", "CCC", "USA", "Parts")
Set rng_reference = Application.InputBox(Prompt:="Please click on any cell in the source database", _
Title:="Click on the source DB", Type:=8).CurrentRegion
With rng_reference
.Rows(1).Resize(, .Columns.Count - 1).Copy _
Destination:=.Cells(1, 1).Offset(, .Columns.Count + 2)
With .Cells(1, 1).Offset(, .Columns.Count + 2)
.Item(1).Offset(1).Resize(, UBound(cond) + 1).Value = cond
Set criteriarng = .CurrentRegion
End With
End With
rng_reference.AdvancedFilter xlFilterInPlace, criteriarng
criteriarng.Clear
MsgBox Application.Subtotal(109, rng_reference.Columns(rng_reference.Columns.Count) )
End Su
-------------------

--
jindo
-----------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...fo&userid=1313
View this thread: http://www.excelforum.com/showthread.php?threadid=38480

Thanks for your time and the suggestions. I'll reconsider using array
formulas.

Have a great summer. :)


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=384804

Including some other stuff, the whole procedure takes much less of my
time. I noticed that the new code utilizes about 80 percent of the CPU
capacity, much efficient than the old code that used about 15 percent.


Thank you.


--
staying
------------------------------------------------------------------------
staying's Profile: http://www.excelforum.com/member.php...o&userid=23230
View this thread: http://www.excelforum.com/showthread...hreadid=384804