Formula will not evaluate in VBA

[mojocojo2000]

I was wishing to test if duplicate names appeared within 2 columns and
take actions accordingly.

Here is the formula I was trying to evaluate.

=((A2:A2000="string")*(B2:B2000="string2"))

When I type the function in manually on an excel sheet I get a
value of 1 which is what I'm desiring.

However, when I use the Evaluate function in VBA a Type mismatch error
is thrown at me.

If Evaluate("=((A2:A2000=" & """" & "string" & """" & ")*(B2:B2000=" _
& """" & "string2" & """" & "))") Then

.....


I can set the evaluate function to a variable, but only if its defined
as a variant and still I can't do anything with the results.

Help would be greatly appreciated. Thanks in advance.

 

[Dave Peterson]

maybe just adding sum() to your expression???

I was wishing to test if duplicate names appeared within 2 columns and
take actions accordingly.

Here is the formula I was trying to evaluate.

=((A2:A2000="string")*(B2:B2000="string2"))

When I type the function in manually on an excel sheet I get a
value of 1 which is what I'm desiring.

However, when I use the Evaluate function in VBA a Type mismatch error
is thrown at me.

If Evaluate("=((A2:A2000=" & """" & "string" & """" & ")*(B2:B2000=" _
& """" & "string2" & """" & "))") Then

....

I can set the evaluate function to a variable, but only if its defined
as a variant and still I can't do anything with the results.

Help would be greatly appreciated. Thanks in advance.

 

[mojocojo2000]

maybe just adding sum() to your expression???











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Wow, thanks Dave... that actually worked!

However are you aware of the explanation for why that is?

 

[Dave Peterson]

This expression:
=((A2:A2000="string")*(B2:B2000="string2"))
results in an array of 1999 0's and 1's.

Not especially useful until you do something else with it.

I bet if you look back at your notes (where you got that array formula), you'll
see either =sum() or =sumproduct() surrounding that worksheet formula.