VBA Array Formulas Running Very Slowly

V

vgresia

New Member
Joined
May 31, 2018
Messages
21
I have two VBA codes that contain multiple formulas for different cells, including array formulas. These codes work, however they run VERY slowly. Any tips on how to speed this up?

The first code essentially enters formulas into a row of cells, and then copies the formula down (there are no arrays in this code).

Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssetCountbyPhase[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Goto[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Reference[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P3"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Scroll[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]True[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Calculation [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlAutomatic

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IF(RC10="""", """", RC10)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Q5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC16, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC16, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC16, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P5:S5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P5:S53"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault

    Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"U5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IF(RC11="""", """", RC11)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"V5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC21, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"W5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC21, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"X5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC21, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"U5:X5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"U5:X53"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Z5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IF(RC12="""", """", RC12)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Z6"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!R[-1]C26, Dashboard!C14, R4C[1], Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AA5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC26, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AB5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC26, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AC5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC26, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Z5:AC5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Z5:AC53"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault


    Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AE5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IF(RC13="""", """", RC13)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AF5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC31, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AG5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC31, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AH5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC31, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AE5:AH5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AE5:AH53"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault


   Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AJ5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IF(RC14="""", """", RC14)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AK5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC36, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AL5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC36, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AM5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C13, Interface!RC36, Dashboard!C14, R4C, Dashboard!C1, ""<>*Master*"", Dashboard!C4, {""Y"",""M""})"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AJ5:AM5"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"AJ5:AM53"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub

[/FONT][/COLOR]</code>
The second, enters in specific formulas into each cell, and includes arrays.

Code: 
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; box-sizing: inherit; white-space: inherit;">[COLOR=#101094][FONT=inherit]Sub[/FONT][/COLOR][COLOR=#303336][FONT=inherit] AssetbyStatus[/FONT][/COLOR][COLOR=#303336][FONT=inherit]()[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Calculation [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlManual

 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Names[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Cat"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Cat"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RefersToR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=Dashboard!R2C9:R100000C9"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

 [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Names[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Linetag"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Linetag"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RefersToR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=Dashboard!R2C3:R100000C3"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

     [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Names[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Incl"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Incl"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RefersToR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=Dashboard!R2C4:R100000C4"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

     [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Names[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"MasterID"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"MasterID"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RefersToR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=Dashboard!R2C1:R100000C1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

     [/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit] ActiveWorkbook[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Names[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Cattype"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Name [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Cattype"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]RefersToR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=Dashboard!R2C10:R100000C10"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]


[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]With[/FONT][/COLOR][COLOR=#303336][FONT=inherit]


    Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R59"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R60"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C4, ""N"", Dashboard!C10, ""Neuro"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R61"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""Ceased"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R62"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""Follow-Up"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R63"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""A&A"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R64"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""Soft A&A"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R65"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""Monday"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R66"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""Meeting"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R67"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""CDA"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R68"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""BB"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R69"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=COUNTIFS(Dashboard!C1, ""<>*Master*"", Dashboard!C10, ""Neuro"", Dashboard!C9, ""TS"")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R70"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=R[-11]C-SUM(R[-10]C:R[-1]C)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S59"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S60"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Incl=""N"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S61"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0, IF(--(ISNUMBER(SEARCH(""Cease"",Cat)))=1,IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S62"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0, IF(--(ISNUMBER(SEARCH(""Follow"",Cat)))=1,IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S63"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""A&A"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S64"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""Soft A&A"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S65"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""Monday"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S66"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""Meeting"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S67"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""CDA"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S68"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""BB"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S69"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=SUM(IF(FREQUENCY(IF(--(ISNUMBER(SEARCH(""Master"",MasterID)))=0,IF(Cat=""TS"",IF(Cattype=""Neuro"",MATCH(Linetag,Linetag,0)))),ROW(Linetag)-ROW(Dashboard!R2C3)+1),1))"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S70"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=R[-11]C-SUM(R[-10]C:R[-1]C)"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IFERROR(INDEX(MasterID,SMALL(IF(ISERROR(SEARCH(R72C16,MasterID))* 
ISNUMBER(SEARCH(R73C16,Cat))*ISNUMBER(SEARCH(R57C16,Cattype))* 
(COUNTIF(R74C16:R[-1]C16,MasterID)=0),ROW(MasterID)- 
ROW(Dashboard!R2C1)+1),1)),"""")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Q75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IFERROR(VLOOKUP(RC[-1], Dashboard!C1:C12, 12, FALSE),"""")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P75:Q75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"P75:Q91"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaArray [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IFERROR(INDEX(MasterID,SMALL(IF(ISERROR(SEARCH(R72C16,MasterID)) 
*ISNUMBER(SEARCH(R73C16,Cat))*ISNUMBER(SEARCH(R57C16,Cattype))* 
(COUNTIF(R74C18:R[-1]C18,MasterID)=0),ROW(MasterID)- 
ROW(Dashboard!R2C1)+1),1)),"""")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]

Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"S75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]FormulaR1C1 [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] _
    [/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"=IFERROR(VLOOKUP(RC[-1], Dashboard!C1:C12, 12, FALSE),"""")"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R75:S75"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Select[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
Selection[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]AutoFill Destination[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"R75:S91"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]),[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Type[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:=[/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlFillDefault

Application[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Calculation [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] xlAutomatic

[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Sub

[/FONT][/COLOR]</code>
I have tried adding in items such as: Application.ScreenUpdating = False Application.DisplayStatusBar = False
as well as playing around with the Manual vs Automatic coding, but did not find any of those to speed it up.

 
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
For your first macro, you have quite a number of Countifs, which are being copied to quite a number of rows. So I think it's bound to be slow. Also, for your second one, your defined ranges span about 100,000 rows. Does your data really cover 100,000 rows? If not, use exact ranges. If the range can change, either convert your data into a Table or use dynamic named ranges. In either case, the range will automatically adjust as data is added/removed.
 
Upvote 0
The other alternative would be to use the DataModel/PowerPivot to achieve your desired results, or maybe even a regular Pivot Table.
Typically all the SUMIFS, SUMPRODUCT and other sheet functions are easily dupllicated in PivotTables. On large datasets, PivotTables will always run faster.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
index match excel formula to vba code
Replies
9
Views
332
DanteAmor
DanteAmor
G
  • Question Question
Improve calculation speed for this worksheet with INDIRECT/FILTER functions
Replies
0
Views
380
GeneBF
G
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
585
RoryA
RoryA
I
  • Question Question
Is there an easy way to add formulaR1C1 into VBA arrays to speed up calculation time?
Replies
4
Views
1K
ianawwalker
I
K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
324
keranali
K

Forum statistics

Threads
1,223,975
Messages
6,175,745
Members
452,667
Latest member
vanessavalentino83

Share this page

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