Array formula not working after running a macro

[John1989]

I have two excel workbooks. Of which one excel (X) contains macro and array formula.

Normally I copy and paste some data from one excel (X) to another excel workbook (Y) manually.

During the manual copy and paste, the array formula works fine.

Later I copied and pasted the same using macros.

But this time the array formula is not working in the excel workbook (X).

It returns as blank.

Still I could see the formula in the the cell but gives no results

The array formula is
=IFERROR(INDEX($A$2:$A$11,SMALL(IF((B$2:B$11>=1)+(B$2:B$11<=-1),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(B$15:B15))),"")

Kindly help.

Thanks in advance
Johnson

 

[AFPathfinder]

I think the formula is still working, but since it's wrapped in the IFERROR function, it's probably getting the #N/A error.

There are multiple ways of copy/pasting data in VBA. What is the syntax you are using for that transfer?

 

[John1989]

I used the following syntax

Windows("Retail.xls").Activate

Sheets("Region").Select

Range("A1:S22").Select

Selection.Copy

Windows("Daily Report.xlsm").Activate

Sheets("Region").Select

Range("A1").Select

ActiveSheet.Paste

Application.CutCopyMode = False

With Selection.Interior

.Pattern = xlNone

.TintAndShade = 0

.PatternTintAndShade = 0

End With

After doing this the array formula is not working


IFERROR(INDEX($A$2:$A$11,SMALL(IF((B$2:B$11>=1)+(B$2:B$11<=-1),ROW($A$2:$A$11)-ROW($A$2)+1),ROWS(B$15:B15))),"")

Please help

 

[AFPathfinder]

I admit I hardly ever deal with array formulas, so this is a bit of trial and error. I think the copy/paste is treating the formula like a normal formula. You'll need to find the R1C1 notation of that formula and then you can add that to the macro to put that formula into the specific column using Range.FormulaArray.

For example, if the formula is supposed to be in column S, then the R1C1 would be:

"=IFERROR(INDEX(R2C1:R11C1,SMALL(IF((R2C[-17]:R11C[-17]>=1)+(R2C[-17]:R11C[-17]<=-1),ROW(R2C1:R11C1)-ROW(R2C1)+1),ROWS(R15C[-17]:R[14]C[-17])))," & """" & """" & ")"

So then, the syntax for inserting the formula would end up being:

Range("S2:S22").FormulaArray = "=IFERROR(INDEX(R2C1:R11C1,SMALL(IF((R2C[-17]:R11C[-17]>=1)+(R2C[-17]:R11C[-17]<=-1),ROW(R2C1:R11C1)-ROW(R2C1)+1),ROWS(R15C[-17]:R[14]C[-17])))," & """" & """" & ")"