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:
.Range(.Cells(2, lastColCopied + 1), .Cells(rowCol(0),
lastColCopied + 1)).FormulaArray = _
"=percentrank(if(periodRange2=RC1,R2C[-1]:R" & rowCol(0) &
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:
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
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,
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1