Excel VBA Check if Postal code falls within Postal Code Range

Hi there,

I am working with a project where I need to lookup a value based on cell value in my data (a postal code, i.e. A0A 1A9), versus a lookup list that is organized by postal code ranges.

Example, the value I am searching for is A0A 1A8 (assume this is in cell A1).

My lookup table is organized as:

[TABLE="width: 500, align: center"]

A0A1A0

A0A1A9

CODE A

A0A1B0

A0A1B9

CODE B

A0A1C0

A0A1E9

CODE C

[/TABLE]

What I am after is being able to return the value in the 3rd column (i.e. "Code A").

The code would need to understand that A0A1A8 falls within the range of (A0A1A0 to A0A1A9), and thus "CODE A" is the return value.

As a second example, if I were to lookup "A0A1D3", the code would need to understand that it falls within the range (A0A1C0 to A0A1E9) because "D" comes before "E" alphabetically and this falls in this range.

Note, the scope of the range is variable as you can see by the 3rd row in my "lookup table" as it spans across 2 alpha in the 5th character slot.

Thanks all!

Re: Excel VBA Check if Postal code falls within Postal Code Range

Hi Jindon,

Pretty close, except for range A0A1C0 to A0A1E9.

If I search for A0A9C1 is returns a no match...

Each character slot can be A-Z and 1-9 so the code A0A9C1 should return CODE C as it is in between A0A1C0 to A0A1E9.

Hope I explained that ok?

Thanks!

Re: Excel VBA Check if Postal code falls within Postal Code Range

Unfortunately none of the digits can be ignored

Example we could have a range of:
A0A1A0 to B0B 1A0

so this would encompass all of the postal codes with A0A as the first three digits, and then all of the combinations for the last 3 digits (i.e. 1A0 (1 could be 1-9), (A could be A-Z), (0 could be 0-9)). Then the firs three digits would change to A0B, and repeat the steps for the last three digits.

Basically any alpha character can be a-z, any digit character can be 0-9, so the code would need to check all 6 digits, not just the last three, to be able to properly figure out if the value we are searching for falls within the range.

Re: Excel VBA Check if Postal code falls within Postal Code Range

Because the range would have went something like

A0A 1C0 - then
1C1
1C2
1C3
1C4
1C5
1C6
1C7
1C8
1C9
THEN
2C0 - and all the combos from 0-9 for the last digit
3C0 - and all the combos from 0-9 for the last digit
4C0 - and all the combos from 0-9 for the last digit
etc.

Once it gets to A0A 9C9 the "C" would then change to

A0A1D0 etc.

Re: Excel VBA Check if Postal code falls within Postal Code Range

Jindon,

Early tests suggest this is working perfectly. Will continue testing and let you know, but from what I can see...AWESOME work again!

Re: Excel VBA Check if Postal code falls within Postal Code Range

Hi Jindon,

It looks like I am going to have to use this as a UDF (user defined function), so the mytxt would be grabbed from the udf input. Tried to revise your code, but getting errors, figured you might be able to understand quicker than I.

thanks!

Re: Excel VBA Check if Postal code falls within Postal Code Range

This is what I have, but am getting #VALUE error when UDF calculates:

Option Explicit


Function PostalCodes(mytxt) As String
Dim a, i As Long, temp As Long, msg As String
a = Range("a1").CurrentRegion.Value
For i = 1 To UBound(a, 1)
    a(i, 1) = GetAsValue(a(i, 1))
    a(i, 2) = GetAsValue(a(i, 2))
Next
temp = GetAsValue(mytxt)
If temp = 0 Then
    MsgBox "Invalid postcode"
    Exit Function
End If
For i = 1 To UBound(a, 1)
    If (Not IsError(a(i, 1))) * (Not IsError(a(i, 2))) Then
        If (temp >= a(i, 1)) * (temp <= a(i, 2)) Then
            msg = a(i, 3)
            Exit For
        End If
    End If
Next


PostalCodes = msg


End Function


Function GetAsValue(ByVal txt As String) As Long
Dim temp
With CreateObject("VBScript.RegExp")
    .Pattern = "^([A-Z])(\d)([A-Z]) ?(\d)([A-Z])(\d)$"
    If .test(txt) Then
        GetAsValue = Asc(.Replace(txt, "$1")) & .Replace(txt, "$2") _
        & Asc(.Replace(txt, "$3")) & Asc(.Replace(txt, "$5")) _
        & .Replace(txt, "$4") & .Replace(txt, "$6")
    End If
End With
End Function

Re: Excel VBA Check if Postal code falls within Postal Code Range

Hi Jindon,

Looks like I ran into a bit of an issue so hoping that you can shed some light.

The postal code I am "searching" is P8N2P4

In my table there is a range P1A1A0 to P8N3L9, but when the code executes it isn't finding this range.

Many many thanks!

Re: Excel VBA Check if Postal code falls within Postal Code Range

Hey Jindon,

Brain fart, you are absolutely correct. Apologies for wasting your time. Must be Monday...and not enough coffee yet today!

Re: Excel VBA Check if Postal code falls within Postal Code Range

Hi Jindon,

It looks like I am just losing it here...been a long week already. P8N2P4 should be in the range P1A1A0 to P8N3L9 because the P8N2P4 would come before P8N3L9 as the fourth character is less than 3. Does that make sense?

Re: Excel VBA Check if Postal code falls within Postal Code Range

Excellent, working great now Jindon, thanks so much!