Evaluate syntax

[kcaisaacs]

I have created a named range referring to a column of numbers and want to create a new array of 1's and 0's in order to isolate a cell containing particular state abbreviations(.ie. make that cell a one when all the others are 0's. I am also trying to do this using the evaluate function:

Please Login or Register  to view this content.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

This returns an array of all zero's even though it should return about 60 1's (states equal to "MA"). I think that the problem is with the use of the double quotations, but I can't get it to work after trying several combinations of quotes.

Any help would be appreciated.

[jindon]

Try change to

Please Login or Register  to view this content.

[shg]

Assign the formula to a string variable, then print it to the Immediate window. If it's correct, you should be able to copy it directly into a cell and have it work. If not, start fixin'.

[Stormin']

Can you not just do this inside VBA instead of using Excel formulas inside VBA?

E.g.

Please Login or Register  to view this content.

[kcaisaacs]

I found the problem. I had to strip the quotes from the state variable:

Please Login or Register  to view this content.

[Stormin']

I think it was Michael Jordan who once said something like "you can practise a thousand shots a day, but if you're using a bad technique all you get good at is shooting a bad ball".

I still think Application.Evaluate is a bad way achieve what you are trying to achieve. *shrugs*

[xlnitwit]

I found the problem. I had to strip the quotes from the state variable:

Please Login or Register  to view this content.

Hi,

That can't have been the problem since there are no quotes within the variable and hence that code achieves nothing at all. I suspect that Jindon's answer was what you actually needed but you just happened to activate the right sheet while testing again.