Moving Array formulas from spreadsheet to VBA
 

Can anyone help me create a VBA procedure that will put the result
of these array forumlas into spreadsheet cells without the formulas
having to go on the sheet.

A B
22-Apr {=COUNT(IF($A$5&$A7=Reviewer&Date_Shipped),1))}
23-Apr {=COUNT(IF($A$5&$A8=Reviewer&Date_Shipped),1))}

This is just one example of a formula. There are similar ones in
columns C, D, E, F, and G. Hopefully, I can extrapolate the code
to include all of them. And hopefully, it will involve looping
because while it was very easy to put this type of formula into
hundreds of cells it has turned out that it makes Excel run very
slowly while they all recalculate.

Moving Array formulas from spreadsheet to VBA
 

John

Try
Sub bbb()
Range("f5").Select
For i = 7 To 9
ActiveCell.Value = Evaluate("=COUNT(IF($A$5&$A" & i
& "=Reviewer&Date_Shipped,1))")
ActiveCell.Offset(1, 0).Select
Next i

End Sub

Tony
-----Original Message-----
Can anyone help me create a VBA procedure that will put
the result
of these array forumlas into spreadsheet cells without
the formulas
having to go on the sheet.

A B
22-Apr {=COUNT(IF($A$5&$A7=Reviewer&Date_Shipped),1))}
23-Apr {=COUNT(IF($A$5&$A8=Reviewer&Date_Shipped),1))}

This is just one example of a formula. There are similar
ones in
columns C, D, E, F, and G. Hopefully, I can extrapolate
the code
to include all of them. And hopefully, it will involve
looping
because while it was very easy to put this type of
formula into
hundreds of cells it has turned out that it makes Excel
run very
slowly while they all recalculate.
.

Moving Array formulas from spreadsheet to VBA
 

Are you clicking a button to run this code? Are you running XL 97? If so, make sure the Take
Focus on Click property of the button is set to False.


On 26 Jul 2003 20:32:16 -0700, (John Pierce) wrote:

Sub bbb()
Range("f5").Select
For i = 7 To 9
ActiveCell.Value = Evaluate("=COUNT(IF($A$5&$A" & i
& "=Reviewer&Date_Shipped,1))")
ActiveCell.Offset(1, 0).Select
Next i

End Sub


Thanks Tony, but for some reason I can't get this to run past the first
line. I get a "Run-time error '1004': Application-defined or
object-defined error". I have other procedures that begin with similar
code so I think there must be something else causing it. Any ideas?
John

Moving Array formulas from spreadsheet to VBA
 

Negatory, Myrna. I'm using Excel 2000 and I'm trying to start the
macro from the VBE by running it or stepping through it. I always
get that error.

Moving Array formulas from spreadsheet to VBA
 

You may have a simple syntax error here.

ActiveCell.Value = Evaluate("=COUNT(IF($A$5&$A" & i
& "=Reviewer&Date_Shipped,1))")

If i = 7, the string will be

=COUNT(IF($A$5&$A7=Reviewer&Date_Shipped,1))

That isn't a valid formula.

If you meant $A$5:$A$7, the first ampersand should be a colon.

I can't be sure the 2nd ampersand is correct, either. In a formula, "Reviewer&Date_Shipped"
would have to be a named range, but it can't be -- ampersands aren't allowed in names.

If Reviewer and Date_Shipped are VBA variables, it definitely *isn't* correct.

My experience with evaluating array formulas in VBA is that it's an iffy proposition. I've had
code that would work one day and crash the next, without any changes being made in between. You
are better off writing this in VBA. Assuming that F5:F7 is supposed to contain running totals of
the matches in A5:A7, A5:A8, and A5:A9, respectively,

Dim Cell As Range
Dim Target As String
Dim N As Long
Dim i AS Long

Set Cell = Range("F5")
Set Target = Reviewer & Date_Shipped
N = 0
For i = 5 To 9
If Cells(i, 1).Value = Target Then N = N + 1
If i = 7 Then
Cell.Offset(i - 7, 0).Value = N
End If
Next i


On Sun, 27 Jul 2003 01:15:39 -0500, Myrna Larson wrote:

Are you clicking a button to run this code? Are you running XL 97? If so, make sure the Take
Focus on Click property of the button is set to False.


On 26 Jul 2003 20:32:16 -0700, (John Pierce) wrote:

Sub bbb()
Range("f5").Select
For i = 7 To 9
ActiveCell.Value = Evaluate("=COUNT(IF($A$5&$A" & i
& "=Reviewer&Date_Shipped,1))")
ActiveCell.Offset(1, 0).Select
Next i

End Sub


Thanks Tony, but for some reason I can't get this to run past the first
line. I get a "Run-time error '1004': Application-defined or
object-defined error". I have other procedures that begin with similar
code so I think there must be something else causing it. Any ideas?
John

Moving Array formulas from spreadsheet to VBA
 

Myrna,
The ampersands are correct. Refer to my first message in this thread
for the array formula. In testing Tony's procedure over and over it
has somehow completely inexplicably gotten over the Runtime error that
came right after the Range reference but the Evaluate step results in
a zero rather than a valid value. By the way, what's going on is that
Reviewer and Shipped_Date are named ranges that are also columns in a
table on a separate sheet. $A$5 is a "criterion" in that it contains
one of the possible entries in the Reviewer column. A7, A8, A99 etc
contain consequtive dates covering the range of dates used in Shipped_
Date. The spreadsheet array formulas will count all items on the other
sheet IF they meet both criteria. They do a nice job of extracting the
data but I want the operation to be invisible to the users, as well as
faster.