Moving Array formulas from spreadsheet to VBA

  • Thread starter Thread starter John Pierce
  • Start date Start date
J

John Pierce

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.
 
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
 
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.
 
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.
 
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
 
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.
 
Back
Top