Hi, I have quite a large VB, and after digging into my code, the calculations seem to be taken the longest.
The worksheet has 1530 lines going from A-AE.
It may not be possible, but is there any way to streamline, improve speed on this section, re-write it in some way ? Its not slow slow, but still a bit slow.
thanks for your help / advice.
David
VBA Code:
Application.DisplayAlerts = False
Application.CutCopyMode = False 'Clearing the Office Clipboard
Application.ScreenUpdating = False ' This turns the screen updating off while the macro runs, helps with speed
Application.EnableEvents = False ' Disables events, so commands where you need to press OK
ActiveSheet.DisplayPageBreaks = False ' Disables page breaks as we wont be printing any data
'Application.Calculation = xlManual ' Turn Off Automatic calulations in Excel
Sheets("Parked Report").Select
Range("N1").Value = "Vendor Name"
Range("O1").Value = "Days Overdue"
Range("P1").Value = "Assigned To"
Range("Q1").Value = "Agent Email"
Range("R1").Value = "User Company"
Range("S1").Value = "Region"
Range("T1").Value = "Scan Date"
Range("U1").Value = "Invoice Amount"
Range("V1").Value = "USD"
Range("W1").Value = "Scan Date (Aging)"
Range("X1").Value = "Status"
Range("Y1").Value = "Overdue (Aging)"
Range("Z1").Value = "Critical /Non Critical"
Range("AA1").Value = "Query User"
Range("AB1").Value = "Comment Date"
Range("AC1").Value = "Standard Comment"
Range("AD1").Value = "Open Comment"
Range("AE1").Value = "Status 2"
Range("AR1").Value = "Type Of Contact"
Range("N2").Select '- Vendor Name column
Selection.FormulaArray = _
"=IFERROR(INDEX(Parked!C[-10],MATCH(1,('Parked Report'!RC[-13]=Parked!C[-13])*('Parked Report'!RC[-11]=Parked!C[-11])*('Parked Report'!RC[-10]=Parked!C[-9]),0)),""Vendor Not Defined"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("N2:N" & last_row)
Erase Array()
Range("O2").Select '"Days Overdue"
ActiveCell.Formula = "=IF(K2<0,-K2,K2)"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("O2:O" & last_row)
Range("P2").Select '"Assigned To"
Selection.FormulaArray = _
"=IFERROR(INDEX(Parked!C[-8],MATCH(1,('Parked Report'!RC[-15]=Parked!C[-15])*('Parked Report'!RC[-13]=Parked!C[-13])*('Parked Report'!RC[-11]=Parked!C[-14]),0)),""Agent not Defined"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("P2:P" & last_row)
Erase Array()
Range("Q2").Select '"Agent Email"
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('User List'!C[-13],MATCH('Parked Report'!RC[-1],'User List'!C[-15],0)),""Agent Email Not Defined"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("Q2:Q" & last_row)
Range("R2").Select 'User Company
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('User List'!C[-15],MATCH('Parked Report'!RC[-1],'User List'!C[-14],0)),""Agent Company Not Assigned"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("R2:R" & last_row)
Range("S2").Select 'Region
ActiveCell.FormulaR1C1 = _
"=IF(OR(LEFT(RC[-18],2)=""US"",LEFT(RC[-18],2)=""CA"",LEFT(RC[-18],2)=""CR""),""NOAM"",""EMEA"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("S2:S" & last_row)
Range("T2").Select 'Scan Date
Selection.FormulaArray = _
"=IFERROR(INDEX(SQ00QR_Query!C[-3],MATCH(1,('Parked Report'!RC[-19]=SQ00QR_Query!C[-18])*('Parked Report'!RC[-17]=SQ00QR_Query!C[-19])*('Parked Report'!RC[-16]=SQ00QR_Query!C[-17]),0)),""Scan Date Not Defined"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("T2:T" & last_row)
Range("U2").Select 'Overdue (Aging)
ActiveCell.FormulaR1C1 = _
"=INDEX('CHF Rate'!C[-18],MATCH('Parked Report'!RC[-14],'CHF Rate'!C[-20],0))*'Parked Report'!RC[-15]"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("U2:U" & last_row)
Range("V2").Select 'USD
ActiveCell.FormulaR1C1 = "=RC[-1]/'CHF Rate'!R2C4"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("V2:V" & last_row)
Range("W2").Select ' Scan Date (Aging)
ActiveCell.FormulaR1C1 = _
"=IFERROR(IF(TodaysDate-'Parked Report'!RC[-3]<=Settings!R6C29,Settings!R6C30,IF(AND(TodaysDate-'Parked Report'!RC[-3]>=Settings!R7C29,TodaysDate-'Parked Report'!RC[-3]<Settings!R8C29),Settings!R7C30,IF(AND(TodaysDate-'Parked Report'!RC[-3]>=Settings!R8C29,TodaysDate-'Parked Report'!RC[-3]<Settings!R9C29),Settings!R8C30,IF(TodaysDate-'Parked Report'!RC[-3]>=Settings!" & _
"R9C29,Settings!R9C30,Settings!R10C30)))),""Scan Date Not Defined"")" & _
""
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("W2:W" & last_row)
Range("X2").Select 'Status
ActiveCell.FormulaR1C1 = "=IF(RC[-13]<=0,""Not Due Yet"",""Overdue"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("X2:X" & last_row)
Range("Y2").Select ' Overdue (Aging)
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC[-10]>=Settings!R6C31,RC[-10]<Settings!R7C31),Settings!R6C32,IF(AND(RC[-10]>=Settings!R7C31,RC[-10]<Settings!R8C31),Settings!R7C32,IF(AND(RC[-10]>=Settings!R8C31,RC[-10]<Settings!R9C31),Settings!R8C32,Settings!R9C32)))"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("Y2:Y" & last_row)
Range("Z2").Select 'Critical /Non Critica
ActiveCell.FormulaR1C1 = _
"=IFERROR(INDEX('Critical Vendor List'!C[-22],MATCH('Parked Report'!RC[-23],'Critical Vendor List'!C[-24],0)),""Non Critical"")"
last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row ' Fill down formula
Selection.AutoFill Destination:=Range("Z2:Z" & last_row)
***********************************************************************
bunch of other code
***********************************************************************
Application.DisplayAlerts = True
Application.CutCopyMode = True 'Clearing the Office Clipboard
Application.ScreenUpdating = True ' This turns the screen updating off while the macro runs, helps with speed
Application.EnableEvents = True ' turn on
ActiveSheet.DisplayPageBreaks = False ' Disables page breaks as we wont be printing any data
Application.CutCopyMode = False ' Clear the memory
The worksheet has 1530 lines going from A-AE.
It may not be possible, but is there any way to streamline, improve speed on this section, re-write it in some way ? Its not slow slow, but still a bit slow.
thanks for your help / advice.
David
Last edited by a moderator: