Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Array Macro
I am trying to create a macro for my personal workbook to use repeately on a
spreadsheet I export from one of my DB's. The spreadsheet is alway's formatted the same, but the data table varies in size in the amount of rows. I need to compare and flag rows based on a simple array formula. Now I am trying to put the array into a macro to hopefully return the results of the formla. At worst I would like the formula copied for my specified range. The formula is - {=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))} It starts on row 6 and the match value ($H6) needs to change with each row. Column "H" may have duplicate entries, why the send IF statement is counting how many "Y"'s are in the corresponding column "P" for each unique entry. My first set of code which I was hoping would just return the results only returned the value of "1" for every cell in the range - Sub eval() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = Answer End Sub The second code I tried was just to copy the array returns the value of "False" Sub Formu() Dim rng As Range Set rng = ActiveSheet.Range("u6") Answer = Evaluate("=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400 =""y"",1,0)))") Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)) = _ FormulaArray = _ "=SUM(IF($H$6:$H$2400=$H6,IF($P$6:$P$2400=""y"",1, 0)))" End Sub Any help would be greatly appreciated. Thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
array formula in macro | Excel Programming | |||
Writing Array formula through macro | Excel Programming | |||
formula array in a macro | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |