PDA

View Full Version : VBA Evaluate function not working in arrays - Need alternate



wezred
06-29-2007, 01:13 AM
My column has 10000 combination in each cell of fruits like apple, banana, orange, kiwi. In the next column there are corresponding numbers. For example:
Apple/Banana 10
Apple/Kiwi 12
Kiwi/Orange 30
Orange/Banana 20
Banana/Apple 19
This formula works and sums up all corresponding columns which cells contain the text "apple":

=+SUM(IF(ISNUMBER(SEARCH("Apple",$A$8:$A$10000)),$B$8:$B$10000))
Answer is 41
I tried creating an evaluation function in VBA as I'm planning to use more than 10 criterias, the function looks like this:

Function Txt2F(InputString As String)
Application.Volatile
Txt2F = Evaluate("=(" & InputString & ")")
End Function
However, the following function doesn't work at all:

=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000))
Where cell $A$1 has the text:
ISNUMBER(SEARCH("Apple",$A$8:$A$10000))
I'd like to create an array such that Cell $A$1 has all the criterias, for example:

ISNUMBER(SEARCH("apple",$a$8:$a$10000))*ISNUMBER(SEARCH("banana",$a$8:$a$10000))+ISNUMBER(SEARCH("kiwi",$a$8:$a$10000))
So the function
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000)) is 71
Anyone here has any ideas or alternatives? I need an evaluation function that works in complex formulas within an array. Thanks.

Shazam
06-29-2007, 06:12 AM
ISNUMBER(SEARCH("apple",$a$8:$a$10000))*ISNUMBER(SEARCH
("banana",$a$8:$a$10 000))+ISNUMBER(SEARCH("kiwi",$a$8:$a$10000))
So the function
=+SUM(IF(Txt2F($A$1),Executive!$B$8:$B$10000)) is 71

How are you getting 71? Because I'm getting 91.