Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range
I've gotten great advice from this group in the past, so I thought I'd
give it another shot with this issue. I have a formula: With Sheets("data") .Range(.Cells(2, lastColCopied + 1), .Cells(rowCol(0), lastColCopied + 1)).FormulaArray = _ "=percentrank(if(periodRange2=RC1,R2C[-1]:R" & rowCol(0) & "C[-1]),RC[-1])" End With I have two columns. One column is 'period', where I store a string such as "Q3-2004". The other column is the data for that period. There are more than one entry for a given period, ie: A B Period Data Q3-2004 3.232 Q3-2004 1.239 Q3-2004 5.326 Q3-2003 5.55 Q3-2003 7.89 ... 150 rows The formula above is supposed to go through the list, pick out all the data elements with the SAME period, and return their PERCENTRANK in the column next to it. The way I do this w/o vba is that I select the cell in the column next to the data, write =percentrank(if(periodRange=$A2,B$2:B$150),B2). I press CTRL+SHIFT to make it an array formula. This returns the percentrank for the ONE value next to it based on the group of all values in the same period. I can then autofill the entire column from that value. The problem with my VBA code above is that it gets confused about which array I am using. When I use the VBA code, I get one big array with all the same values in it. I cannot change any element of that big array, like I can when I do it the regular way and autofill. What am I missing on this one? Thanks in advance, Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Named Range within an Array Formula | Excel Discussion (Misc queries) | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Issue Adding an Array Formula to a cell at Runtime. | Excel Discussion (Misc queries) | |||
different formula for ROW, array / range? | Excel Discussion (Misc queries) | |||
Array Formula, noncontigous range | Excel Worksheet Functions |