Case statement in variable range

  • Thread starter Thread starter TP
  • Start date Start date
T

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
 
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
Click to expand...

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
 
Back
Top