Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
Need formula to look up the zip codes listed in H2 thru K1522 when an address is entered. For example: In Cell B2 the following is entered: 2005 Airline Rd Cell B3 return zip code: 75605 Zip code list: _ __|____H____|___I___|__J__|__K___ _1|_Street___|__Zip__|Begin|_End__ 10|_Adrian_Rd_|_75605_|_0000_|_0000 11|_Agness_Dr|_75602_|_0000_|_0000 12|_Airline_Rd_|_75603_|_0001_|_1999 13|_Airline_Rd_|_75605_|_2000_|_9999 14|_Akinships_|_75605_|_0000_|_0000 15|_Albertata_|_75605_|_0000_|_0000 16|_Aledo_Str_|_75604_|_0000_|_0000 17|_Alexander_|_75604_|_0000_|_0000_ The street name must not only be looked up, but if begin & end numbers exists in colums J & K, they must be used to determine the zip code too. Thanks so very much for your help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
Mike,
The following UDF will return the ZIp Code. It requires a named range (Street) - column H and anothe called "ZipCode_Table" - columns H to K, both starting row 1. HTH Function GetZipCode(zrng) As String Dim v(1) As Variant, zTab As Variant zTab = Range("ZipCode_Table") n = InStr(1, zrng.Value, " ") v(0) = Left(zrng.Value, n - 1) ' Street Number v(1) = Right(zrng.Value, Len(zrng.Value) - n) 'Street Name ZipCode = Application.Match(v(1), Range("Street"), 0) If IsError(ZipCode) Then GetZipCode = "Street not found" Exit Function End If nz = Application.CountIf(Range("Street"), v(1)) If nz = 1 Then GetZipCode = zTab(ZipCode, 2) Else nrow = ZipCode For i = 1 To nz If CInt(v(0)) <= zTab(nrow, 4) Then GetZipCode = zTab(nrow, 2) Exit Function End If nrow = nrow + 1 Next i End If End Function Sub test() MsgBox GetZipCode(Range("a1")) End Sub "mikeburg" wrote: Need formula to look up the zip codes listed in H2 thru K1522 when an address is entered. For example: In Cell B2 the following is entered: 2005 Airline Rd Cell B3 return zip code: 75605 Zip code list: _ __|____H____|___I___|__J__|__K___ _1|_Street___|__Zip__|Begin|_End__ 10|_Adrian_Rd_|_75605_|_0000_|_0000 11|_Agness_Dr|_75602_|_0000_|_0000 12|_Airline_Rd_|_75603_|_0001_|_1999 13|_Airline_Rd_|_75605_|_2000_|_9999 14|_Akinships_|_75605_|_0000_|_0000 15|_Albertata_|_75605_|_0000_|_0000 16|_Aledo_Str_|_75604_|_0000_|_0000 17|_Alexander_|_75604_|_0000_|_0000_ The street name must not only be looked up, but if begin & end numbers exists in colums J & K, they must be used to determine the zip code too. Thanks so very much for your help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
See response in excel.misc
-- HTH Bob Phillips (remove nothere from the email address if mailing direct) "mikeburg" wrote in message ... Need formula to look up the zip codes listed in H2 thru K1522 when an address is entered. For example: In Cell B2 the following is entered: 2005 Airline Rd Cell B3 return zip code: 75605 Zip code list: _ __|____H____|___I___|__J__|__K___ _1|_Street___|__Zip__|Begin|_End__ 10|_Adrian_Rd_|_75605_|_0000_|_0000 11|_Agness_Dr|_75602_|_0000_|_0000 12|_Airline_Rd_|_75603_|_0001_|_1999 13|_Airline_Rd_|_75605_|_2000_|_9999 14|_Akinships_|_75605_|_0000_|_0000 15|_Albertata_|_75605_|_0000_|_0000 16|_Aledo_Str_|_75604_|_0000_|_0000 17|_Alexander_|_75604_|_0000_|_0000_ The street name must not only be looked up, but if begin & end numbers exists in colums J & K, they must be used to determine the zip code too. Thanks so very much for your help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
This worked for me and considered both streets with multiple entries and
streets with 0000 0000 in both begin and end. =IF(VLOOKUP((MID(B2,FIND(" ",B2)+1,255)),$H$2:$K$1522,3,FALSE)=0,VLOOKUP((MID (B2,FIND(" ",B2)+1,255)),$H$2:$K$1522,2,FALSE),SUMPRODUCT (--($H$2:$H$1522=MID(B2,FIND(" ",B2)+1,255)),--((--LEFT(B2,FIND(" ",B2)-1))=$J$2:$J$1522),--((--LEFT(B2,FIND(" ",B2)-1))<=$K$2:$K$1522),$I$2:$I$1522)) -- Regards, Tom Ogilvy "mikeburg" wrote in message ... Need formula to look up the zip codes listed in H2 thru K1522 when an address is entered. For example: In Cell B2 the following is entered: 2005 Airline Rd Cell B3 return zip code: 75605 Zip code list: _ __|____H____|___I___|__J__|__K___ _1|_Street___|__Zip__|Begin|_End__ 10|_Adrian_Rd_|_75605_|_0000_|_0000 11|_Agness_Dr|_75602_|_0000_|_0000 12|_Airline_Rd_|_75603_|_0001_|_1999 13|_Airline_Rd_|_75605_|_2000_|_9999 14|_Akinships_|_75605_|_0000_|_0000 15|_Albertata_|_75605_|_0000_|_0000 16|_Aledo_Str_|_75604_|_0000_|_0000 17|_Alexander_|_75604_|_0000_|_0000_ The street name must not only be looked up, but if begin & end numbers exists in colums J & K, they must be used to determine the zip code too. Thanks so very much for your help. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
You guys are a great help. In trying to decide which way to go, what would be VBA code to accomplish arriving at the zip code. If possible, I would like the VBA code to be the sheets change code. When the street is keyed in, the corresponding zip code is looked up & put by it. Don't give up on me. I am learning a lot from y'all. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need formula to look up zip codes
Right click on the sheet tab and select view code. Put in code like this:
Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim v As Variant, sVal As String Dim sStr As String, lVal As Long Dim sName As String, i As Long If Target.Address = "$B$2" Then v = Range("$H$2:$K$1522") sVal = Left(Target, InStr(1, _ Target.Value, " ", vbTextCompare) - 1) lVal = CLng(sVal) sName = Right(Target, Len(Target) - (Len(sVal) + 1)) sStr = "Not Found" For i = 1 To UBound(v, 1) If StrComp(sName, v(i, 1), vbTextCompare) = 0 Then If CLng(v(i, 3)) = 0 Or _ (lVal = CLng(v(i, 3)) And _ lVal <= CLng(v(i, 4))) Then sStr = Format(v(i, 2), "00000") Exit For End If End If Next Range("B3").Value = sStr End If End Sub -- Regards, Tom Ogilvy "mikeburg" wrote in message ... You guys are a great help. In trying to decide which way to go, what would be VBA code to accomplish arriving at the zip code. If possible, I would like the VBA code to be the sheets change code. When the street is keyed in, the corresponding zip code is looked up & put by it. Don't give up on me. I am learning a lot from y'all. mikeburg -- mikeburg ------------------------------------------------------------------------ mikeburg's Profile: http://www.excelforum.com/member.php...o&userid=24581 View this thread: http://www.excelforum.com/showthread...hreadid=503623 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to VBA codes | Excel Discussion (Misc queries) | |||
Convert Column Formula into VBA codes | Excel Discussion (Misc queries) | |||
Tricky Formula Codes ***Urgent*** | Excel Discussion (Misc queries) | |||
Formula to look up zip codes | Excel Discussion (Misc queries) |