Fill down an array formula across multiple worksheets

AggyRJ

New Member
Joined
Mar 29, 2013
Messages
14
In short, what I need to be able to do is fill an array formula down in multiple worksheets. Within each sheet the formula needs to fill down a different number of times. I will give some background info so that this can be understood in context and then paste the code I currently have below that.

I am creating a macro that has a list of zip codes that are assigned to specific templates. The full list with template names and zip codes is in a worksheet titled ORDR Info. In a previous step, I have written a code which creates worksheets where each one is named with the template name and cell I1 has the template name. I need to move the zip codes that match the template name to the worksheet for that template (later I will export each worksheet as a separate .csv file).

In order to import the zip codes into each worksheet I am using an array formula in cell R1 which looks in the worksheets “ORDR Info” and returns the zip codes where the template matches the value in I1. The actual formula works fine, however the problem is that the array formula must be dragged down the same number of zip codes so that they are displayed in the list. For example, a template named “027_570” I have 115 zip codes, so I need the formula to populate in R1 and then fill down an additional 114 times.

What I thought I would do is create a helper cell in I2 using COUNTIF to return a value which represents how many times the template name is found and then have the array formula fill down that number of times. My issue is that I cannot figure out how to make this work with an array formula. The code I am using enters the formula as an array and fills it down the correct number of times, however it does it as a regular formula, not an array. My hope is that just that single line of code can be updated because everything else works.



Any help with this is GREATLY appreciated.

Code:
Sub Import_Zips()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Activate
'Count the number of times the template name is shown in the Zones list
    Range("I2").Select
    ActiveCell.FormulaR1C1 = "=IF(COUNTIF('ORDR Info'!C1,R1C9)=0,1,COUNTIF('ORDR Info'!C1,R1C9)-1)"
    Range("I2").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
'Array formula to find the match of template name and zip code
    Range("R1").Select
    Selection.FormulaArray = "=IFERROR(INDEX('ORDR Info'!C2,SMALL(IF(R1C9='ORDR Info'!C1,ROW('ORDR Info'!C1)-ROW('ORDR Info'!R2C1)+1),ROW(R[1]))),"""")"
'Fill down array formula the number of rows equal to the number of zip codes found in the template
    Range("R1:R" & Range("I2").Value).Formula = Range("R1").Formula
    Next
    
    Application.ScreenUpdating = True
    
End Sub
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,257
Office Version
365
Platform
Windows
Hi AggyRJ,
try something like this (untested):

Code:
Sub Import_Zips()

    Application.ScreenUpdating = False
    
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        'Count the number of times the template name is shown in the Zones list
        ws.Range("I2").FormulaR1C1 = "=IF(COUNTIF('ORDR Info'!C1,R1C9)=0,1,COUNTIF('ORDR Info'!C1,R1C9)-1)"
        ws.Range("I2").Copy
        ws.Range("I2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        ws.Calculate 'Not sure if this is needed, but to make sure the value is updated
        'Array formula to find the match of template name and zip code
        'Fill down array formula the number of rows equal to the number of zip codes found in the template
        If ws.Range("I2").Value >= 1 Then
            ws.Range("R1:R" & ws.Range("I2").Value).FormulaArray = "=IFERROR(INDEX('ORDR Info'!C2,SMALL(IF(R1C9='ORDR Info'!C1,ROW('ORDR Info'!C1)-ROW('ORDR Info'!R2C1)+1),ROW(R[1]))),"""")"
        End If
    Next
    
    Application.ScreenUpdating = True
    
End Sub
Note that I did make some changes: when you record a macro a lot of .select and .activate statements are saved, but generally it is good practice not to use them when avoidable.

Hope this helps,
Koen
 

Watch MrExcel Video

Forum statistics

Threads
1,095,211
Messages
5,443,098
Members
405,215
Latest member
Sheshashayan M V

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top