VBA for inserting array formula without looping?

[Excel43]

Hi, I have a macro that inserts an array formula in a big number of cells. It works, but if there is a lot of data (over 3000 rows), it gets super slow and takes about 20 minutes to finish, and very often excel tends to die in the process.

Is it possible to speed it up? If so, how can I achieve that?

This is what I have:

Sheets("Settings").Range("A" & Rows.count).End(xlUp).Offset(1, 0).Select

     Selection.FormulaArray = _
    "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"

    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":A600")

 

[johnnyL]

How about:

'
'   Turn Settings off
      Application.ScreenUpdating = False                                            ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                              ' Turn AutoCalculation off
        Application.EnableEvents = False                                            ' Turn EnableEvents off
    Application.DisplayStatusBar = False                                            ' Turn DisplayStatusBar off
'
'
  With Sheets("Settings")
    LastRowPlus1 = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A" & LastRowPlus1 & ":A600").FormulaArray = "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"
  End With
'
'   Turn Settings back on
    Application.DisplayStatusBar = True                                             ' Turn DisplayStatusBar back on
        Application.EnableEvents = True                                             ' Turn EnableEvents back on
         Application.Calculation = xlCalculationAutomatic                           ' Turn AutoCalculation back on
      Application.ScreenUpdating = True                                             ' Turn Screen Updating back on

 

[Fluff]

You cannot enter an array formula into multiple cells at once. So about all you can do is turn calculation off at the start & turn it on again at the end.

 

[johnnyL]

You cannot enter an array formula into multiple cells at once. So about all you can do is turn calculation off at the start & turn it on again at the end.

What does that mean? "multiple cells at once"?

 

[Marcelo Branco]

What does that mean? "multiple cells at once"?

See the topic 3.2 - Setting a Multi-Cell Array formula

Working With Range.FormulaArray In VBA

1 Introduction The Range.FormulaArray property is used to set or return the array formula of a range and works in a very similar way to the more familiar Range.Formula property. However, because of…

colinlegg.wordpress.com

M.

 

[johnnyL]

Ok, had some time time to play with this today. Got a chance to run some timings also.

Code from the Original post:

Sheets("Settings").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Select

     Selection.FormulaArray = "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"

    ActiveCell.AutoFill Destination:=Range(ActiveCell.Address & ":A600")

Timing results:

Fastest Code that I tested, is actually a loop code:

    Dim LastRowPlus1    As Long
    Dim RowCounter      As Long
'
    LastRowPlus1 = Sheets("Settings").Range("A" & Sheets("Settings").Rows.Count).End(xlUp).Row + 1
'
    For RowCounter = LastRowPlus1 To 600
        Sheets("Settings").Range("A" & RowCounter).FormulaArray = "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"
    Next

Timing Results:

Next fastest option:

    Dim LastRowPlus1    As Long
'
    LastRowPlus1 = Sheets("Settings").Range("A" & Sheets("Settings").Rows.Count).End(xlUp).Row + 1
'
    Sheets("Settings").Range("A" & LastRowPlus1 & ":A600").FormulaR1C1 = "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"
    Sheets("Settings").Range("A" & LastRowPlus1 & ":A600").FormulaArray = Sheets("Settings").Range("A" & LastRowPlus1 & ":A600").FormulaR1C1

Timing Results:

Next Fastest Option:

    Dim LastRowPlus1    As Long
'
    LastRowPlus1 = Sheets("Settings").Range("A" & Sheets("Settings").Rows.Count).End(xlUp).Row + 1
'
    Sheets("Settings").Range("A" & LastRowPlus1).FormulaArray = "=IFERROR(INDEX(data!R3C8:R3700C8, MATCH(0,IF(ISBLANK(data!R3C8:R3700C8),1,COUNTIF(R1C1:R[-1]C, data!R3C8:R3700C8)), 0)),"""")"
    Sheets("Settings").Range("A" & LastRowPlus1).Copy Sheets("Settings").Range("A" & LastRowPlus1 + 1 & ":A600")

Timing Results:

I'm still not sure what qualifies as "Multiple cells at once", but maybe one of those versions of code qualifies.

 

[Fluff]

I'm still not sure what qualifies as "Multiple cells at once"

It means more than one cell.
In the main an array formula should only be entered into one cell at a time.