I would approach it in steps.
First, I'd get a formula that worked by doing it manually--not in code.
I'd start with something like:
=sumproduct(--($h$1:$h$999=h1),--($p$1:$p$999="y"))
Adjust the ranges to match--but you can't use whole columns (except in xl2007).
=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.
Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
=====
Once you get a formula that works, you can modify the macro to use it.
GregK wrote:
I am trying to add that array formula to a dynamic range. If Column "A" has
a value, put this formula in column "U" (what I prefer are the actually
results of the formula). That part seems to work fine. What I am not
getting are the correct results from the formula (which works fine if I
manually input the formula in "U"). Perhaps there's even a better way to
accomplish what I am trying to do.
Column "H" has a list of addresses which in many cases duplicate them selves
(the dupes are necessary as they contain other data that's important). In
column "P" there may be one of three values for that address specific to that
row of data. The values are "Y", "N" or blank. If address "1 Tree CT"
appears three times throughout my table in column "H" I need to count how
many times a "Y" appears in column "P" for that address. I then want that
number to appear in column "U". So if "1 Tree Ct" appears 3 times say in
"H8" and has a "Y" in "P8" then the addy appears on "H9" and has a "N" in
"P9", and in cell 'H10" the addy appears again and has a "Y" in "P10". The
values in "U8, U9, U10" should all have a "2" since two "Y"'s appear for that
1 address. I hope that makes a little more sense.
"Dave Peterson" wrote:
I'm confused over what you want.
This is the kind of syntax you'd want--but I'm not sure it's what you really
want.
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)))"
GregK wrote:
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!
--
Dave Peterson
--
Dave Peterson