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