Array formula not working after running a macro

John1989

New Member
Joined
Aug 21, 2017
Messages
22
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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:
Code:
"=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:
Code:
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])))," & """" & """" & ")"
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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