Vba to create a new column and insert array formula

Question

My below code is the slowest sub in my workbook. I can't set it to put the Array in immediatley, because the mode is calculated incorrectly (calculates for just one site no)

Is there a way to cut this down without breaking the fully functioning code?

Sub ModeColumn()
Dim wb As Workbook, ws As Worksheet, LastRow As Long, rng As Range
Set wb = ThisWorkbook
Set ws = Worksheets("Data")
Set rng = ws.Cells(2, 15)
 ws.Cells(1, 15) = "MODE"
 LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'Finds the bottom populated row
 With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
 .Formula = "=IFERROR(MODE(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "C12)),""N/A"")"
 .FormulaArray = .FormulaR1C1
 End With
End Sub

Here's what it looks like working

enter image description here enter image description here

So above we can see that the selection works perfectly, but if I use the array formula right away, I get a lot of errors - so my time saving, breaks the expected result And here's what it looks like if I just use "array" straight away

enter image description here enter image description here

This is based on the following code

With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
 .FormulaArray = "=IFERROR(MODE(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "C12)),""N/A"")"
End With

P.S I cannot (by request) use Excel Tables to make this easier

• IvenBach: Just from the images in the top you have $L2ドル:$L3774ドル and below it you have $L2ドル:$L7. Seems like something was done that's not reflected in your code. Was there a manual edit?
• Badja: Sorry, should have mentioned - the last 2 images are from the "record macro" feature, where I added in the Array Forumla after
• Badja: I have edited my question to reflect the bad code correctly

Answer 1

Usually with mass-Formula, I would recommend judicious use of the Application.Calculation property to ensure that you don't recalculate for every cell (3773 times in your example!) - but, in this case I am also going to recommend using the Range.FillDown method instead of assigning the Array Formula to every cell in the range.

(Also the MODE function is only included for Legacy Support, and has been replaced by the MODE.SNGL and MODE.MULT functions)

Application.Calculation = xlCalculationManual 'Do not recalculate formula until we say so!
With ws.Range(ws.Cells(2, 15), ws.Cells(LastRow, 15))
 .Cells(1,1).FormulaArray = "=IFERROR(MODE.SNGL(IF($M2=AllSites,$L2ドル:$L$" & LastRow & ")),""N/A"")" 'Set the Array Formula for the first cell
 '.Cells(1,1).FormulaArray = "=IFERROR(MODE.SNGL(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "C12)),""N/A"")"'In R1C1 Notation
 .FillDown 'Copy the Array Formula down to the end
End With
Application.Calculation = xlCalculationAutomatic 'Reset Calculation Mode

(For comparison - running your method on 99999 rows of junk data took me 105 seconds. Using the .Filldown method on the same data took 29 seconds, and 99% of that was just waiting for Excel to finish calculating the Worksheet after the functions where in!)

• Badja: Hi - thanks for the great insight. Clearly looks like a much better way to run this. I'm not sure what I need to do with Application.Calculate as it just gives me a compile error. Apologies for this, I've never used this feature
• Badja: Got it, I used Application.Calculation = xlCalculationManual instead
• Chronocidal: @Badja Yes, I was just about to say that - I typed the wrong one when writing it out for your code. Sorry!
• Chronocidal: @Badja The Range.FormulaArray property is known to sometimes have... issues... with R1C1 notation, which is why I rewrote "=IFERROR(MODE.SNGL(IF(RC[-2]=AllSites,R2C12:R" & LastRow & "C12)),""N/A"")" as "=IFERROR(MODE.SNGL(IF($M2=AllSites,$L2ドル$L$" & LastRow & ")),""N/A"")" (the same, but in A1 notation)
• Chronocidal: I was missing the colon from "$L2ドル:$L$" & LastRow