Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case statement in variable range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Case statement in variable range
On Sat, 23 Aug 2003 10:22:11 -0500, "TP" wrote:
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","So uth","Southwest","West","Northwest","North") --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Case Statement | Excel Discussion (Misc queries) | |||
IF STATMENT OR CASE STATEMENT | Excel Discussion (Misc queries) | |||
Anyone actually get Case statement to work ... ? | Excel Worksheet Functions | |||
CASE statement equivalent | Excel Worksheet Functions | |||
Passing a variable to a range statement | Excel Programming |