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:
Now , the formula I let the procedure enter takes some time I guess:
the last part of this formula "DATABASE!$F$4:$AJ$10000" are volumes in a table that are lookuped with following formula:
the runtime of the macro becomes worse when I want this last formula to be flexible, using an indirect vlookup like this:
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?
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:
Code:
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:
Code:
=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:
Code:
=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:
Code:
=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?