Case statement in variable range

[TP]

Using VBA, how would I populate one cell based on the contents of another
with a Case statement?

For example, if a1 = 555 S, b1 should = "South"
a2 = 333 N, b2 should = "North"

I cannot nest an If statement in a formula, because there are more than 7
scenarios.

**Important to note that the range will vary - is there a for i to row
count statement that can be used?

Thanks in advance!

Patti

[Ron Rosenfeld]

Post by TP
Using VBA, how would I populate one cell based on the contents of another
with a Case statement?
For example, if a1 = 555 S, b1 should = "South"
a2 = 333 N, b2 should = "North"
I cannot nest an If statement in a formula, because there are more than 7
scenarios.
**Important to note that the range will vary - is there a for i to row
count statement that can be used?
Thanks in advance!
Patti

In general, you would run a SUB and set the value property of the cell equal to
your test result. And yes you can activate the SUB to test any particular
range, or the Selection.

If you presented more information, I might be able to give a more detailed
answer. In addition, depending on your algorithm, it may be possible to devise
a formula to do what you want.

I'm not sure of the algorithm from what you've posted. But formulas using
LOOKUP, MATCH, INDEX, could possibly all do what you want. From the limited
examples you present, here are two possible solutions:


=INDEX({"North","East","South","West"},MATCH(RIGHT(A10,1),{"N","E","S","W"},0))

=CHOOSE(ROUND(MOD(LEFT(A10,3),360)/45,0)+1,"North","Northeast","East","Southeast","South","Southwest","West","Northwest","North")


--ron