More validadtion problems!

[Les Isaacs]

Bob

OK - the penny has dropped: I now understand that your method works by
formatting an invalid postcodes in a way that differentiates them from valid
postcodes. Sorry I was a bit slow to see this!

I have opened your example in excel (thanks for the suggestion Peo!), and I
can see your method working ... except that it does seem to allow some
invalid postcodes (e.g. CH47 54R, and CFR 7BT). I have tried looking at the
list of defined names to see if I can work out how to amend them to correct
these problems, but I'm afraid the code is beyond me (but I hope you'll
agree that I've come a long way from when I was reluctant to even try using
your code!).

The other issue here is that I have also been trying to implement an
alternative solution, from Nick Rothstein in this thread, which looks like
it will work through data validation and so would in fact be preferable (as
it would prevent invalids rather than highlighting them). So although I am
certainly very grateful to you for all your help with this I think that
perhaps I will now run with Rick's solution: I hope you are not offended!

In case you are interested (as I certainly still am) in why your method
isn't working in my workbook I am emailing it to you seperately.

Many thanks once again.
Les

 

[Rick Rothstein \(MVP - VB\)]

Having said that, I may need to make a minor alteration to your code,

because the Post Office have altered the rules since you wrote it! (or
perhaps your code is in fact more up to date than the rules at
http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that
these rules are dated Sept 2002).

According to these rules the letters M, N and some others should not be
allowed in the third position, but I think your code allows any letter in
that position. I have had a look at your code hoping to be able to 'spot'
how to update if if necessary, but it was beyond me! If you could guide me
in the right direction I would be very grateful (although I know I need
first to find a more up-to-date ruleset!).

Nope, I missed that particular restriction when I first investigated the UK
Postcodes. The modification to my function was real easy to do (Like
operator comparisons are relatively easy to work with) and I have included
it below.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Les Isaacs]

Rick

Thanks for this.
The amended code is now working ... except that it seems to be rejecting
every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try to see
why this is happening!
I hope you don't give up on me!
Thanks again
Les

Having said that, I may need to make a minor alteration to your code,
because the Post Office have altered the rules since you wrote it! (or
perhaps your code is in fact more up to date than the rules at
http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that
these rules are dated Sept 2002).

According to these rules the letters M, N and some others should not be
allowed in the third position, but I think your code allows any letter in
that position. I have had a look at your code hoping to be able to 'spot'
how to update if if necessary, but it was beyond me! If you could guide
me in the right direction I would be very grateful (although I know I
need first to find a more up-to-date ruleset!).

Nope, I missed that particular restriction when I first investigated the
UK Postcodes. The modification to my function was real easy to do (Like
operator comparisons are relatively easy to work with) and I have included
it below.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

The amended code is now working ... except that it seems to be rejecting

every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try to
see why this is happening!

The function appears to be working fine here. You are putting the function
in a Module that you add inside the VBA editor (Insert/Module from its menu
bar), right?

Rick

 

[Les Isaacs]

Rick

OK - my mistake - sorry!
Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd
position.
Does this happen at your end?

Thanks as ever
Les

 

[Bob Phillips]

No of course not, it is your problem, you need to call it. Good luck.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Les,

Just to jump in, I installed Rick's UDF, put it as a formula in a hidden
column as suggested and then referred to that hidden column in the DV and it
worked fine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Rick

Thanks for this.
The amended code is now working ... except that it seems to be rejecting
every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try to
see why this is happening!
I hope you don't give up on me!
Thanks again
Les

Having said that, I may need to make a minor alteration to your code,
because the Post Office have altered the rules since you wrote it! (or
perhaps your code is in fact more up to date than the rules at
http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that
these rules are dated Sept 2002).

According to these rules the letters M, N and some others should not be
allowed in the third position, but I think your code allows any letter
in that position. I have had a look at your code hoping to be able to
'spot' how to update if if necessary, but it was beyond me! If you could
guide me in the right direction I would be very grateful (although I
know I need first to find a more up-to-date ruleset!).

Nope, I missed that particular restriction when I first investigated the
UK Postcodes. The modification to my function was real easy to do (Like
operator comparisons are relatively easy to work with) and I have
included it below.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Bob Phillips]

Rather than a hidden column, you could also use worksheet change with the
function

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "E:E" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If Not ValidatePostCode(.Value) Then
MsgBox "Invalid Postcode"
.Value = ""
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Les,

Just to jump in, I installed Rick's UDF, put it as a formula in a hidden
column as suggested and then referred to that hidden column in the DV and
it worked fine.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Rick

Thanks for this.
The amended code is now working ... except that it seems to be rejecting
every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try to
see why this is happening!
I hope you don't give up on me!
Thanks again
Les

Having said that, I may need to make a minor alteration to your code,
because the Post Office have altered the rules since you wrote it! (or
perhaps your code is in fact more up to date than the rules at
http://www.govtalk.gov.uk/gdsc/html/frames/PostCode.htm. - I note that
these rules are dated Sept 2002).

According to these rules the letters M, N and some others should not be
allowed in the third position, but I think your code allows any letter
in that position. I have had a look at your code hoping to be able to
'spot' how to update if if necessary, but it was beyond me! If you
could guide me in the right direction I would be very grateful
(although I know I need first to find a more up-to-date ruleset!).

Nope, I missed that particular restriction when I first investigated the
UK Postcodes. The modification to my function was real easy to do (Like
operator comparisons are relatively easy to work with) and I have
included it below.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Bob Phillips]

Until Rick signs in, try this amendment

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

OK - my mistake - sorry!

Does that mean you hadn't put it in a Module and that it works now that you
have?

Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd
position.
Does this happen at your end?

Yes, it happens here too. I'm guessing the site I used for a reference
either didn't mention these restrictions or had it buried in a spot that I
overlooked. Try the new function code below my signature and see if I
trapped everything now. By the way, that long listing in my code was
developed against actually assigned postcodes in the UK proper itself as I
remember; but they all seemed to be of the AAN* variety, so I am guessing
those other formats are used elsewhere.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9ABCDEFGHJKSTUW]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9ABEHMNPRVWXY]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Rick Rothstein \(MVP - VB\)]

By the way, that long listing in my code was developed

against actually assigned postcodes in the UK proper
itself as I remember; but they all seemed to be of the
AAN* variety, so I am guessing those other formats
are used elsewhere.

What the above comment was supposed to make clear (but, in re-reading it,
didn't) was this... those postcodes beginning with AAN are being checked
against actually assigned postcodes, so if the passed in string fails one of
those tests, it is because that letter-letter combination is not (presently)
assigned. However, the other formats being checked are for shape only
(although you correctly pointed out some errors in how I implement them), so
it is possible for the code to say a certain postcode is valid when it
actually isn't assigned anywhere. If I had a complete list of **all**
assigned postcodes, I could modify the function to be more accurate.

Rick

 

[Bob Phillips]

Rick,

have you checked my suggestion?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

have you checked my suggestion?

Do you mean the posting that starts off with "Until Rick signs in, try this
amendment"? If so, I looked at the function you posted and I don't see where
it differs from the first function I posted. What am I missing?

Rick

 

[Leslie Isaacs]

Rick

We're definitely getting there!

My previous mistake was simply misaligning the validation column with the
postcode column - so I had AW2=ValidatePostCode(I1), etc. Now sorted.

With your new code the 3rd position is validated OK, but the following rules
appear not to be:

a.. The letters Q, V and X are not used in the first position.
a.. The letters I, J and Z are not used in the second position.
a.. In the second half of the Postcode the letters C, I, K, M, O and V are
never used.

I wish I could understand your code sufficiently to know how to amend it,
but I can't! Perhaps when I see how you amend it for these 3 rules
(hopefully!) I will be closer to it.

Thanks once again
Les

OK - my mistake - sorry!

Does that mean you hadn't put it in a Module and that it works now that
you have?

Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd
position.
Does this happen at your end?

Yes, it happens here too. I'm guessing the site I used for a reference
either didn't mention these restrictions or had it buried in a spot that I
overlooked. Try the new function code below my signature and see if I
trapped everything now. By the way, that long listing in my code was
developed against actually assigned postcodes in the UK proper itself as I
remember; but they all seemed to be of the AAN* variety, so I am guessing
those other formats are used elsewhere.

Rick

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9ABCDEFGHJKSTUW]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9ABEHMNPRVWXY]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" Or _
Parts(1) Like "*#[!CIKMOV][!CIKMOV]")
End If
End Function

 

[Leslie Isaacs]

Bob
Many thanks for this.
I have tested lots of postcodes with your code, and the only problem I have
been able to find so far is that it does not allow an A in the first
position (which should be valid). Again, I wish I could understand the code
sufficiently to know how to amend it for this, but I can't get it!
Thanks for not giving up on me!
Les

Until Rick signs in, try this amendment

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Rick

OK - my mistake - sorry!
Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd
position.
Does this happen at your end?

Thanks as ever
Les

 

[Bob Phillips]

I have changed the last Or to an And, between Part(0) and Parts(1). I found
that A3Z 3AA passed because the last part was valid.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Leslie Isaacs]

Rick

I am surprised that you would want to check against actually assigned
postcodes, because new addresses are being created all the time (as our
greenbelt turns grey!), and so new, valid postcodes - according to the
ruleset - will need to pass the test. Or am I missing something?

Les

 

[Bob Phillips]

Les,

It allows AB1 1MA, AL1 1MA but no other A* combination. What should it allow
starting with A?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob
Many thanks for this.
I have tested lots of postcodes with your code, and the only problem I
have been able to find so far is that it does not allow an A in the first
position (which should be valid). Again, I wish I could understand the
code sufficiently to know how to amend it for this, but I can't get it!
Thanks for not giving up on me!
Les

Until Rick signs in, try this amendment

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Rick

OK - my mistake - sorry!
Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the 3rd
position.
Does this happen at your end?

Thanks as ever
Les


message The amended code is now working ... except that it seems to be
rejecting every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try
to see why this is happening!

The function appears to be working fine here. You are putting the
function in a Module that you add inside the VBA editor (Insert/Module
from its menu bar), right?

Rick

 

[Leslie Isaacs]

Bob
Any of the AANN ANN formats - .e.g. AC47 7BT should be OK, but is rejected.
Les

Les,

It allows AB1 1MA, AL1 1MA but no other A* combination. What should it
allow starting with A?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob
Many thanks for this.
I have tested lots of postcodes with your code, and the only problem I
have been able to find so far is that it does not allow an A in the first
position (which should be valid). Again, I wish I could understand the
code sufficiently to know how to amend it for this, but I can't get it!
Thanks for not giving up on me!
Les

Until Rick signs in, try this amendment

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Rick

OK - my mistake - sorry!
Using the new code you gave however still seems to be allowing A3Z 3AA,
etc - i.e. with a prohibited Z (and other prohibited letters) in the
3rd position.
Does this happen at your end?

Thanks as ever
Les


message The amended code is now working ... except that it seems to be
rejecting every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could try
to see why this is happening!

The function appears to be working fine here. You are putting the
function in a Module that you add inside the VBA editor (Insert/Module
from its menu bar), right?

Rick

 

[Bob Phillips]

Well this works for those but it is not really my code so I may break
something else.

This code as it stands allows A and any second letter except I,J,Z.

What about A#, is that valid?

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[ABCDEFGHKLMNOPQRSTUVWXY]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob
Any of the AANN ANN formats - .e.g. AC47 7BT should be OK, but is
rejected.
Les

Les,

It allows AB1 1MA, AL1 1MA but no other A* combination. What should it
allow starting with A?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Bob
Many thanks for this.
I have tested lots of postcodes with your code, and the only problem I
have been able to find so far is that it does not allow an A in the
first position (which should be valid). Again, I wish I could understand
the code sufficiently to know how to amend it for this, but I can't get
it!
Thanks for not giving up on me!
Les


Until Rick signs in, try this amendment

Function ValidatePostCode(ByVal PostCode As String)
Dim Parts() As String
PostCode = UCase$(PostCode)
Parts = Split(PostCode)
If PostCode = "GIR 0AA" Or PostCode = "SAN TA1" Or _
(Parts(1) Like "#[A-Z][A-Z]" And _
(Parts(0) Like "[A-Z]#" Or _
Parts(0) Like "[A-Z]#[0-9A-Z]" Or _
Parts(0) Like "[A-Z][A-Z]#" Or _
Parts(0) Like "[A-Z][A-Z]#[0-9A-Z]")) Then
ValidatePostCode = (Parts(0) Like "[BEGLMSW]#*" Or _
Parts(0) Like "A[BL]#*" Or _
Parts(0) Like "B[ABDHLNRST]#*" Or _
Parts(0) Like "C[ABFHMORTVW]#*" Or _
Parts(0) Like "D[ADEGHLNTY]#*" Or _
Parts(0) Like "E[CHNX]#*" Or _
Parts(0) Like "F[KY]#*" Or _
Parts(0) Like "G[LU]#*" Or _
Parts(0) Like "H[ADGPRSUX]#*" Or _
Parts(0) Like "I[GPV]#*" Or _
Parts(0) Like "K[ATWY]#*" Or _
Parts(0) Like "L[ADELNSU]#*" Or _
Parts(0) Like "M[EKL]#*" Or _
Parts(0) Like "N[EGNPRW]#*" Or _
Parts(0) Like "O[LX]#*" Or _
Parts(0) Like "P[AEHLOR]#*" Or _
Parts(0) Like "R[GHM]#*" Or _
Parts(0) Like "S[AEGKLMNOPRSTWY]#*" Or _
Parts(0) Like "T[ADFNQRSW]#*" Or _
Parts(0) Like "W[ACDFNRSV]#*" Or _
Parts(0) Like "UB#*" Or _
Parts(0) Like "YO#*" Or _
Parts(0) Like "ZE#*" And _
(Parts(1) Like "*#[!CIKMOV][!CIKMOV]"))
End If
End Function


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

Rick

OK - my mistake - sorry!
Using the new code you gave however still seems to be allowing A3Z
3AA, etc - i.e. with a prohibited Z (and other prohibited letters) in
the 3rd position.
Does this happen at your end?

Thanks as ever
Les


in message The amended code is now working ... except that it seems to be
rejecting every postcode that I eneter (even my own!).
I wish I could understand the code a little better - then I could
try to see why this is happening!

The function appears to be working fine here. You are putting the
function in a Module that you add inside the VBA editor
(Insert/Module from its menu bar), right?

Rick