assign value to range

[Justin]

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

[Tom Ogilvy]

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(B1>467,B1<567),And(B1>855,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

[rockofaith]

Yeah...I'm the rockofaith/Justin guy. I figured I'd ask on the site that looked like the microsoft site...but it put the question here. Interesting.

[Crowbar via OfficeKB.com]

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 [email protected]

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

[Executor]

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

[rockofaith]

the email I see for Crowbar is split up...is it prec_tec?

Executor...I don't see a range capability?

[Executor]

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

[rockofaith]

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 [email protected]

Thanks and sorry, it's taking me a while to get this

[Executor]

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