slow response time calculating

[shodan]

Hi guys,

After some help from all of you on the board, I was able to set up a database using quit a lot of functions, and one small macro to be carried out. Now, this macro is quit slow to be carried out. please see the code i have:

Sub Print_All_Items()
Dim itemrange As Range
Dim itemcounter As Integer
Dim volumeversie As String

Range("D2").Value = "Your request is now processing.  Please wait...."
Range("D2").Select
With Selection.Font
.ColorIndex = 5
.Size = 12
End With
Range("A1").Select
Application.ScreenUpdating = False

volumeversie = Worksheets("MRP").Range("D8").Value

'toevoegen en opmaak pagina
Worksheets("VOLUMES").Activate
Worksheets("VOLUMES").Range("ITEMS").Select
Selection.Copy
Worksheets.Add
With ActiveSheet
.Paste
.Name = "Printout"
End With

With ActiveSheet.PageSetup
.CenterHeader = "&""Tahoma,Vet""&14Overview " & volumeversie
.LeftFooter = "Created by J.Meynen"
.RightFooter = "&D &T "
End With

Set itemrange = Range("A1").CurrentRegion
itemcounter = itemrange.Rows.Count

'plaatsen van de formula
Range("C1").Select
For i = 1 To itemcounter
Cells(i, 3).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A" & i & ",(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))" ' this formula is to be replaced by the large sumif formula
Next i

'copy/paste formulas to values and remove zero lines

Worksheets("Printout").Activate
Range(Cells(1, 1), Cells(itemcounter, 3)).Select
With Selection
.Copy
.Range("A1").PasteSpecial xlPasteValues
.Application.CutCopyMode = False
.Sort Key1:=Range("C1"), Order1:=xlDescending
End With

Range(Cells(1, 3), Cells(itemcounter, 3)).NumberFormat = "#,##0"

'Delete de lege rijen
For i = itemcounter To 1 Step -1 'itemcounter
If Cells(i, 3).Value = 0 Then Rows(i).Delete
Next i

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Application.DisplayAlerts = False
Worksheets("Printout").Delete
Worksheets("MRP").Activate
MsgBox "Your request has been processed.  Please get your copy at the printer"

Range("D2").ClearContents

Application.DisplayAlerts = True
Application.ScreenUpdating = True


End Sub

Now , the formula I let the procedure enter takes some time I guess:

=SUM(IF(DATABASE!$E$4:$AJ$10000=MRP!D19;(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))

the last part of this formula "DATABASE!$F$4:$AJ$10000" are volumes in a table that are lookuped with following formula:

=SUMPRODUCT(--(VOLUMES!$A$1:$A$10000=DATABASE!A4);--(VOLUMES!$B$1:$B$10000=DATABASE!B4);(VOLUMES!$C$1:$C$10000))

the runtime of the macro becomes worse when I want this last formula to be flexible, using an indirect vlookup like this:

=SUMPRODUCT(--(VOLUMES!$A$11:$A$10000=DATABASE!A4);--(VOLUMES!$B$11:$B$10000=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$A$1:$B$2;2;FALSE)))

In plain excell, everything works fine, but of course that is just calculated for one cell, but using the macro combined with the most flexible formulas, the response time is verry bad.

Even if I do save as "new name" it start calculating and it takes a whole lot of time for the sheet to be saved with a new name again. When I want a new version of the file I have to use the explorer and copy and rename it there. The file is only about 3mega.

Does anyone see things about my code that are bad (i'm very new in vba)
or ways for improvement?

 

[Aladin Akyurek]

...
I use us a sumproduct formula, (see my first example). but when i make it variable, using an indirect vlookup to determin the range to be summed, it is than the the macro runs slow...

What do you have exactly in VOLUMES!$A$1:$B$2?

 

[shodan]

Aladin, thanks for having a look at my post.

There is one thing I must say, when i posted my thread, it used two workbooks to copy the formula from so the original formula I posted:

=SUMPRODUCT(--(VOLUMES!$A$11:$A$10000=DATABASE!A4);--(VOLUMES!$B$11:$B$10000=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$A$1:$B$2;2;FALSE)))

should really be:

=SUMPRODUCT(--(VOLUMES!$A$1:$A$10000=DATABASE!A4);--(VOLUMES!$B$1:$B$10000=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$AA$1:$AB$2;2;FALSE)))

This just to make my ranges clear and to avoid misunderstandings.

To answer your question:

In this range VOLUMES!$A$1:$B$2 or now VOLUMES!$AA$1:$AB$2? is the lookuptable i use to make the indirect function to work:

Period1 VOLUMES!$C$1:$C$10000
Period2 VOLUMES!$G$1:$G$10000

Hope this is clear, Thanks a lot for your help.

 

[Aladin Akyurek]

Aladin, thanks for having a look at my post.

There is one thing I must say, when i posted my thread, it used two workbooks to copy the formula from so the original formula I posted:

=SUMPRODUCT(--(VOLUMES!$A$11:$A$10000=DATABASE!A4);--(VOLUMES!$B$11:$B$10000=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$A$1:$B$2;2;FALSE)))

should really be:

=SUMPRODUCT(--(VOLUMES!$A$1:$A$10000=DATABASE!A4);--(VOLUMES!$B$1:$B$10000=DATABASE!B4);INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$AA$1:$AB$2;2;FALSE)))

This just to make my ranges clear and to avoid misunderstandings.

To answer your question:

In this range VOLUMES!$A$1:$B$2 or now VOLUMES!$AA$1:$AB$2? is the lookuptable i use to make the indirect function to work:

Period1 VOLUMES!$C$1:$C$10000
Period2 VOLUMES!$G$1:$G$10000

Hope this is clear, Thanks a lot for your help.

You want speed, right?

1.

Replace the INDIRECT(VLOOKUP(Sheet4!$D$9;VOLUMES!$AA$1:$AB$2;2;FALSE)) bit with:

IF(Sheet4!$D$9="Period1";VOLUMES!$C$1:$C$10000;VOLUMES!$G$1:$G$10000)

2. For more speed:

On VOLUMES...

J1 (assuming that J is available, otherwise pick another column), copied down:

=A1&"#"&B1

Then invoke:

=SUMIF(VOLUMES!$J$1:$J$10000;DATABASE!A4&"#"&DATABASE!B4;IF(Sheet4!$D$9="Period1";VOLUMES!$C$1:$C$10000;VOLUMES!$G$1:$G$10000))

 

[shodan]

Aladin, can I ask you some additional questions before I go and do my homework?

1) What if I have more than 2 periods? how do I cover that with the if function.
2) how comes that you can get the if function to work? I thought to get a text part to be used as a part of the formula, you had to use the indirect function?
3)Is it correct that the last part of your solution should replace the sumproduct formula which I now use?
4) I learnd about the sumproduct formula here on the board, and was estonished about its possibilities, but with the last solution you gave me, is what you are saying that rather than using a multiconditional sumproduct, use a sumif on a concatenation of data?

Thanks a lot !!! really appreciate it

 

[Aladin Akyurek]

Aladin, can I ask you some additional questions before I go and do my homework?

1) What if I have more than 2 periods? how do I cover that with the if function.

CHOOSE(MATCH(Period;{"Perod1"\"Period2"\"Period3"},0);RangeForPeriod1;RangeForPeriod2;RangeForPeriod3)

2) how comes that you can get the if function to work? I thought to get a text part to be used as a part of the formula, you had to use the indirect function?

The IF idiom returns the real range the formula must process. This eliminates the need for the volatile INDIRECT.

3)Is it correct that the last part of your solution should replace the sumproduct formula which I now use?

Yes.

4) I learnd about the sumproduct formula here on the board, and was estonished about its possibilities, but with the last solution you gave me, is what you are saying that rather than using a multiconditional sumproduct, use a sumif on a concatenation of data?

Large ranges, multiple conditions, and having the required formula in too many cells together affects the response time adversely. Whenever the speed becomes an issue, switching to formulas that compute on range objects is a right thing to do.

 

[shodan]

Aladin, Many thanks !!

Only after reading your second reply I realized that the 2 solutions you gave me could not be both applied because they are both to or:
1) make the sumproduct more effective by removing the indirect and replacing it by a sumif/choose
2)replace the sumproduct completely.

I went for option 1, being the remove of the indirect and replace it by the sumif. This works just great. Even so great that it takes me less time (1sec) using a dynamic sumproduct like:

=SUMPRODUCT(--(VOLUMES!$A$1:$A$10000=DATABASE!A4);--(VOLUMES!$B$1:$B$10000=DATABASE!B4);(IF(MRP!$D$9="PLOP2";VOLUMES!$C$1:$C$10000;VOLUMES!$G$1:$G$10000)))

rather than I work with a fixed sumproduct like:

=SUMPRODUCT(--(VOLUMES!$A$1:$A$10000=DATABASE!A4);--(VOLUMES!$B$1:$B$10000=DATABASE!B4);(VOLUMES!$C$1:$C$10000))

Only the last part I did not comprehend very well:

Aladin posted :

Large ranges, multiple conditions, and having the required formula in too many cells together affects the response time adversely. Whenever the speed becomes an issue, switching to formulas that compute on range objects is a right thing to do.

Thanks a lot for your help.[/i][/b]

 

[Aladin Akyurek]

Aladin, Many thanks !!

...

You're welcome.

Only the last part I did not comprehend very well:

Aladin posted :

Large ranges, multiple conditions, and having the required formula in too many cells together affects the response time adversely. Whenever the speed becomes an issue, switching to formulas that compute on range objects is a right thing to do.

A SumProduct or a {Sum(If(...))} formula that consists of many conditionals which tests large ranges like 50000 rows and copied to 1000 or more cells is bound to affect the performance adversely.