Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
Array formulas slows spreadsheet | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
Moving Formulas | Excel Discussion (Misc queries) |