Largest part of code VB formulas speed issues

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78
Hi, I have quite a large VB, and after digging into my code, the calculations seem to be taken the longest.

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:

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,303
Office Version
  1. 2010
Platform
  1. Windows
it is often the way that projects get 'mission creep' LOL i am happy to explain code blocks i use if you want to improve your skills
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78
Yes please deffinatly, I have build allot now, though nothing on this level. I'm the goto person so I need to up my game, company is a great company they are going to sponsor me to do a course, im going to get through this project first, the one pager is yet to be built the coding is the same as the parked just different layouts, so crack one crack the other.

I tried using profiling but I didn't get far, as I couldn't get a report out if it. Code blocks sound very interesting, it's OK writing code but its the junk code and the streamlining efficiency thats the hard bit.

Thanks

DAVE.
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78

ADVERTISEMENT

. oops ignore

For some reason this only calculates the first line, then copies the same all the way down, but will only use the first line as a the loockup - A2, C2, E2, so all the values are the same



Sheets("Parked Report").Range("N1:AF1") = _
Array("Vendor Name", "Days Overdue", "Assigned To", "Agent Email", _
"User Company", "Region", "Scan Date", "Invoice Amount", "USD", "Scan Date (Aging)", _
"Status", "Overdue (Aging)", "Critical /Non Critical", "Query User", "Comment Date", _
"Standard Comment", "Open Comment", "Status 2", "Conditional Status ")


last_row = ActiveSheet.Cells(Rows.Count, "c").End(xlUp).row
Range("N2:N" & last_row).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"")"
Range("O2:O" & Cells(Rows.Count, "c").End(xlUp).row).Formula = "=IF(K2<0,-K2,K2)"

Range("P2:P" & last_row).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"")"

Range("Q2:Q" & last_row).FormulaR1C1 = _
"=IFERROR(INDEX('User List'!C[-13],MATCH('Parked Report'!RC[-1],'User List'!C[-15],0)),""Agent Email Not Defined"")"

Range("R2:R" & last_row).FormulaR1C1 = _
"=IFERROR(INDEX('User List'!C[-15],MATCH('Parked Report'!RC[-1],'User List'!C[-14],0)),""Agent Company Not Assigned"")"

Range("S2:S" & last_row).FormulaR1C1 = _
"=IF(OR(LEFT(RC[-18],2)=""US"",LEFT(RC[-18],2)=""CA"",LEFT(RC[-18],2)=""CR""),""NOAM"",""EMEA"")"

Range("T2:T" & last_row).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"")"

Range("U2:U" & last_row).FormulaR1C1 = _
"=INDEX('CHF Rate'!C[-18],MATCH('Parked Report'!RC[-14],'CHF Rate'!C[-20],0))*'Parked Report'!RC[-15]"

Range("V2:V" & last_row).FormulaR1C1 = "=RC[-1]/'CHF Rate'!R2C4"

Range("W2:W" & last_row).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"")" & _
""

Range("X2:X" & last_row).FormulaR1C1 = "=IF(RC[-13]<=0,""Not Due Yet"",""Overdue"")"

Range("Y2:Y" & last_row).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)))"

Range("Z2:Z" & last_row).FormulaR1C1 = _
"=IFERROR(INDEX('Critical Vendor List'!C[-22],MATCH('Parked Report'!RC[-23],'Critical Vendor List'!C[-24],0)),""Non Critical"")"
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78
if I try fill down manually it says you cant change part of an array
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78

ADVERTISEMENT

Got the speed issue sorted with a help columns.
 

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
78

mohadin - thanks - it didnt work like I thought it would, but by addding help columns and referencing those, I was able to cut down the formulas, and speed it up allot, I was also able to modify other modules to speed them up to, and get rid of code I didnt need or could get round.​

 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
@Dave01
Hi
Glade you sorted out
Thank you for the feed back
Be happy @ safe
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,168
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top