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 |
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 |
All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com