Largest part of code VB formulas speed issues

Dave01

Board Regular
Joined
Nov 30, 2018
Messages
116
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:
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
 
Upvote 0

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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.
 
Upvote 0
. 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"")"
 
Upvote 0
if I try fill down manually it says you cant change part of an array
 
Upvote 0
Got the speed issue sorted with a help columns.
 
Upvote 0

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.​

 
Upvote 0
@Dave01
Hi
Glade you sorted out
Thank you for the feed back
Be happy @ safe
 
Upvote 0

Forum statistics

Threads
1,213,504
Messages
6,114,016
Members
448,543
Latest member
MartinLarkin

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
Back
Top