Formula Array Macro

[GregK]

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]

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]

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]

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 here:
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]

The formula I currenly have works fine when I type it manually. Are saying
that I cannot get my current formula to work with VBA? I am a complete
novice when it comes to VBA so I am not sure how formula array's and VBA
interact with one another. I would think though that if it works when typed
in, it should with VBA as well.

 

[GregK]

I finally got my code to work with my current array, would I like to do next
is instead of my array formula being entered into the cells (column U) I
would just liek the results fo the formula. The one problem I am going to
have is the middle part of my formula - "=$H6" needs to increment with each
row. Which is why I have my code the way it is now (although when I don't
use the formulaarray= as I do on other spreadsheets of mine, the relative
references do change, not sure why they don't here).

Here's what I have so far -

Sub formAry()

Dim rng As Range
Set rng = ActiveSheet.Range("u6")

Range("u6").FormulaArray = _
"=SUM(IF($H$6:$H$2400=H6,IF($P$6:$P$2400=""y"",1,0)))"

rng.AutoFill Range(rng, rng.Offset(0, -20).End(xlDown).Offset(0, 20)),
xlFillSeries


End Sub

The formula I currenly have works fine when I type it manually. Are saying
that I cannot get my current formula to work with VBA? I am a complete
novice when it comes to VBA so I am not sure how formula array's and VBA
interact with one another. I would think though that if it works when typed
in, it should with VBA as well.