| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
is number between to other numbers.
In collumn a i have the zipcode a city starts with (4 numbers)
In collumn b i have the ziptcode a city ends with (4 numbers) In collumn c i have the citynames etc etc Is there a way to check is a zipcode entert belong to a city? there are zipcodes not used? Greetings Hans |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is number between to other numbers.
Dim zip as String, z1 as Long, cell as Range
zip = "12345" z1 = clng(Left(zip,4)) for each cell in Range(A2:A500") if z1 = cell and z1 <= cell.offset(0,1) then msgbox "City is " & cell.offset(0,2) exit for end if Next -- regards, Tom Ogilvy "hans" wrote: In collumn a i have the zipcode a city starts with (4 numbers) In collumn b i have the ziptcode a city ends with (4 numbers) In collumn c i have the citynames etc etc Is there a way to check is a zipcode entert belong to a city? there are zipcodes not used? Greetings Hans |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is number between to other numbers.
=IF(ISNA(MATCH(1,(K1=A1:A10)*(K1<=B1:B10),0)),"No
match",INDEX(C1:C10,MATCH(1,(K1=A1:A10)*(K1<=B1:B 10),0))) where K1 is the zipcode entered -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "hans" wrote in message ... In collumn a i have the zipcode a city starts with (4 numbers) In collumn b i have the ziptcode a city ends with (4 numbers) In collumn c i have the citynames etc etc Is there a way to check is a zipcode entert belong to a city? there are zipcodes not used? Greetings Hans |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is number between to other numbers.
Thank.
Only Bob's solution has an error. Greetings Hans "hans" schreef in bericht ... In collumn a i have the zipcode a city starts with (4 numbers) In collumn b i have the ziptcode a city ends with (4 numbers) In collumn c i have the citynames etc etc Is there a way to check is a zipcode entert belong to a city? there are zipcodes not used? Greetings Hans |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
is number between to other numbers.
Probably NG wrap.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "hans" wrote in message ... Thank. Only Bob's solution has an error. Greetings Hans "hans" schreef in bericht ... In collumn a i have the zipcode a city starts with (4 numbers) In collumn b i have the ziptcode a city ends with (4 numbers) In collumn c i have the citynames etc etc Is there a way to check is a zipcode entert belong to a city? there are zipcodes not used? Greetings Hans |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to get the numbers above and below a number | Excel Worksheet Functions | |||
| HOW DO I GET LIKE INVOICE NUMBERS TO GO TO THE NEXT NUMBER? | New Users to Excel | |||
| IF a number is in between two numbers.... | Excel Discussion (Misc queries) | |||
| number of spaces between numbers | Excel Discussion (Misc queries) | |||
| Can I re-number the row numbers? | Excel Worksheet Functions | |||
![hans[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
