Forum Discussion

David's avatar
David
Copper Contributor
Sep 08, 2017

Macro for an array formula

I have a product sales spreadsheet which is generated monthly. I also have a rebate report which is also generated monthly. I inserted an array formula into a new column (column E) in the sales spreadsheet which checks each row of data find a match of the customer name, catalog # and order number. If it find a match, it will insert the customer name into the row in Column E.

 

The formula in COL E in the products sales report is is as follows: =INDEX('Rebate Report'!$A$4:$L$396,MATCH(1,('Rebate Report'!A:A=B2)*('Rebate Report'!B:B=C2)*('Rebate Report'!C:C=D2),0),1)

 

I want to create a macro so that a user can click the command button and it will insert the row E into the product sales spreadsheet and calculate the above array formula into each row as shown below each month. The rebate report and the products sales reports have a different number of rows each month. I can make the range in the formula(i.e. currently it is 'Rebate Report'!$A$4:$L$396 in the formula) for the rebate report way bigger than will ever be needed e.g. 5000 rows if that makes things simpler than making it dynamic.  

Thanks
David

 

The data looks like this:

Rebate Report

COL A

Customer Name.............

COL B

Catalog Number..........................

COL C

Invoice#.......

Contract No..........ShipDate.UPCQty Shpd.. Actual Cost..  Rebate Cost  Dif Amt......  Rebate Ext... 
DEF CO10250Q1643NS1206376.0130034731/18/20177821134567-8        67.60                   30.63        36.97      (295.76)

 

 

Product Sales Report             

COL A

Customer ID

COL B
Customer Name

COL C
Catalog #		COL D
Order Number		COL E
On rebate report

COL F

Price Line

Product IDDescriptionShipping BranchShip Qty
1110ABC CO10250T1S1210754.001ABC COXXX8286XYZ150
1661DEF CO10250T1S1210215.001#NAXXX8286XYZ1

2

 

Excel Desktop
Macros & VBA

2 Replies

Sort By
  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Sep 11, 2017

    Try using something like this.  Not sure how you plan on inserting rows....but this will get you the array formula via VBA.  You'll need to define the last row of your data so the formula extends the correct length:

     


    Sub Test()

     'Define Lrow based on data in column A
     Lrow = Worksheets("Rebate Report").Cells(Rows.Count, "A").End(xlUp).Row
     
     'Enter array formula based on lastrow
     Range("E2:E" & Lrow).FormulaArray = "=INDEX('Rebate Report'!$A$4:$L$" & Lrow & ",MATCH(1,('Rebate Report'!A:A=B2)" _
     & "*('Rebate Report'!B:B=C2)*('Rebate Report'!C:C=D2),0),1)"

    End Sub

     

Resources