Solved

# Macro to populate formula based on value in cell M2

Posted on 2016-08-19
48 Views
Hi Experts Using Excel 2013

need a macro to populate down the following array formula down column A starting at Cell A8.....

So if the value in cell M2 is 74 then populate down starting at A8 and incl A8 74 times if Cell m2 is 84 then so on..

=IFERROR(INDEX('Unique List'!\$B\$2:\$B\$2000,SMALL(IF('Unique List'!\$A\$2:\$A\$2000=\$B\$3,ROW('Unique List'!\$A\$2:\$A\$2000)-ROW('Unique List'!\$A\$2)+1),ROWS(B\$8:B8))),"")
0
Question by:route217
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 18

Expert Comment

ID: 41762161
Why don't you just copy the formula down? Once you enter it as an array formula, in the initial cell, you can simply copy it down like any other formula
0

Author Comment

ID: 41762167
Need the spread sheet to be dynamic...I have 650...company's...so everything I chang the select from the data validation list..I do not want to keep on coping the formula down..
0

LVL 18

Accepted Solution

xtermie earned 500 total points
ID: 41762217
Ok, use something like this:
'substitute for your column, formulas etc
Application.ScreenUpdating = False
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A8").Formula = "=\$L\$1/\$L\$2" 'substitute your formula '<-- you can skip this is you have the formula as an array formula in the spreadsheet
Range("A8").AutoFill Destination:=Range("A2:A" & lastRow)
0

Author Comment

ID: 41762221
let me test...not sure i follow the instructions..
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
###### Suggested Courses
Course of the Month3 days, 10 hours left to enroll