Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
I have a column with the first three numbers of a zip code. Each of these zip
codes is in a particular zone, but it's not as easy as 00001-00004 = zone 1, 00005-00010 = zone 2...they're all over the place. I need somehow to have in the zone column a function that says if the number zip column falls into a particular set of ranges then zone # will = 2-12. I just don't know how to set up a bunch of ranges. Without knowing how to do it...my mind would say do something like IF(B1 = {001,300}, {467,567}, {855, 964}), "zone 1" - with the ranges being 001 to 300, 467 to 567, and so on. I know that's not the way to do it, of course...but that's what I need to know. Thanks so much |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
What you describe is called a lookup table. Apparently someone else is
struggling with a similar problem in an earlier thread under the name rockoffaith. Maybe you should consult with them although they don't seem to have much more to offer in the form of specifics than you. Using an if statement would work if you only have 7 different conditions. =IF(Or(And(B1 1 and B1 < 300), And(B1467,B1<567),And(B1855,B1<964)), "zone 1",If(or(And(B1=300,B1<400),And(B1=567,B1<=600)) ,"zone2,If( . . . -- Regards, Tom Ogilvy "Justin" wrote: I have a column with the first three numbers of a zip code. Each of these zip codes is in a particular zone, but it's not as easy as 00001-00004 = zone 1, 00005-00010 = zone 2...they're all over the place. I need somehow to have in the zone column a function that says if the number zip column falls into a particular set of ranges then zone # will = 2-12. I just don't know how to set up a bunch of ranges. Without knowing how to do it...my mind would say do something like IF(B1 = {001,300}, {467,567}, {855, 964}), "zone 1" - with the ranges being 001 to 300, 467 to 567, and so on. I know that's not the way to do it, of course...but that's what I need to know. Thanks so much |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
Yeah...I'm the rockofaith guy. I figured I'd ask on the site that looked like the microsoft site...but it put the question here. Interesting. -- rockofaith ------------------------------------------------------------------------ rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494 View this thread: http://www.excelforum.com/showthread...hreadid=542628 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
This will do it, its configured for sheet 2 but you will get my idea
If you want my test workbook I can email it to you prec Sub Macro2() Dim LastRow LastRow = Sheet2.UsedRange.Row + 1 _ + Sheet2.UsedRange.Rows.Count For x = 1 To LastRow If Val(Sheet2.Cells(x, 1).Value) "0" And Val(Sheet2.Cells(x, 1).Value) < "11" Then Sheet2.Range("B" & x).Value = "Engalnd" ElseIf Val(Sheet2.Cells(x, 1).Value) "10" And Val(Sheet2.Cells(x, 1). Value) < "21" Then Sheet2.Range("B" & x).Value = "Scotland" ElseIf Val(Sheet2.Cells(x, 1).Value) "20" And Val(Sheet2.Cells(x, 1). Value) < "31" Then Sheet2.Range("B" & x).Value = "Ireland" End If Next End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200605/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
Hi Justin
I suggest you use something like this: Create a list of codes and zones like, keeping at least one row free at the top: ___|__A__|___B____| _1_|_____|________| _2_|_001_|_Zone_1_| _3_|_301_|_Zone_2_| _4_|_467_|_Zone_1_| _5_|_568_|_Zone_3_| _6_|_855_|_Zone_1_| _7_|_964_|_Zone_2_| The function for the Zone Column would be: Assuming the Zip code is in Cell D1 =OFFSET($B$1,MATCH(D1,$A$2:$A$7,1),0) HTH, Executor |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
the email I see for Crowbar is split up...is it prec_tec? Executor...I don't see a range capability -- rockofait ----------------------------------------------------------------------- rockofaith's Profile: http://www.excelforum.com/member.php...fo&userid=3449 View this thread: http://www.excelforum.com/showthread.php?threadid=54262 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
Hi,
You can simply copy my function down. The function will change into =OFFSET($B$1,MATCH(D1,$A$2:$A$7,1),0) =OFFSET($B$1,MATCH(D2,$A$2:$A$7,1),0) =OFFSET($B$1,MATCH(D3,$A$2:$A$7,1),0) =OFFSET($B$1,MATCH(D4,$A$2:$A$7,1),0) and so on... Makes this things clear? Executor |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
I still don't get it. If I could get a spread sheet with an example of how this would work maybe it'll help me understand what's going on..?? my email is Thanks and sorry, it's taking me a while to get this -- rockofaith ------------------------------------------------------------------------ rockofaith's Profile: http://www.excelforum.com/member.php...o&userid=34494 View this thread: http://www.excelforum.com/showthread...hreadid=542628 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
assign value to range
Hi,
Your email looks not complete. In my first reply you see the upperleft corner of a sheet. Format column A as text. Fill is the zipcodes. Fill column B with the zones Fill column D with the zipcoes you want indicated Fill column E with my formula. Greetings Executor |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I assign range to variant and use | Excel Discussion (Misc queries) | |||
Assign a Name to a Range in VBA | Excel Programming | |||
assign value to range in VBA function | Excel Programming | |||
assign a value to a range | Excel Discussion (Misc queries) | |||
Assign a random selection to a range | Excel Programming |