I have a stupid question

[LisaContractorUmbrella]

Morning chaps,

I am quite happy to admit that I am rubbish with VBA code and therefore I need your help - I got this code from t'interweb:

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

Which is then used in conjunction with a formula within conditionally formatting.

The stupid question is - is there anything within that code that indicates where the data is in the workbook?

[mudskipper]

Morning chaps,

The stupid question is - is there anything within that code that indicates where the data is in the workbook?

It isn't - it's a function, so you pass in the data you want to perform the function on.

In the same way as you'd use, e.g. =ISNUMBER(A1)

You'd do something like =ValidPostcode(A1)

Does that make sense?

(Disclaimer - I do not have a minimum of 2 years of professional VBA expertise)

[LisaContractorUmbrella]

Thanks Mudskipper I have tried that: Function ValidPostCode(O2:O5000) but it's coming up with 'compile error expected: List separator or )'

[mudskipper]

Thanks Mudskipper I have tried that: Function ValidPostCode(O2:O5000) but it's coming up with 'compile error expected: List separator or )'

You need to run it on one cell - it just takes in one value.

Try =ValidPostCode(O2) for example.

[LisaContractorUmbrella]

You need to run it on one cell - it just takes in one value.

Try =ValidPostCode(O2) for example.

Thanks again - it's accepted (O2) but nothing seems to be happening - does it need to be 'run' in the same way as a macro or should it just do something

[TheFaQQer]

Thanks again - it's accepted (O2) but nothing seems to be happening - does it need to be 'run' in the same way as a macro or should it just do something

It depends what you want it to do

I'm assuming you have a column in a spreadsheet which you want to indicate whether it's valid or not. If you want to show whether it's OK or not, you could insert a column next to it and put something like

=IF(ValidPostCode(A1),"OK","Not OK")

as the formula (assuming column A is where your postcodes are).

Or you could use conditional formatting and use a similar formula,

=IF(ValidPostCode(A1),1,0)

which would highlight the cells that are OK and leave the others alone. If you want to mark the incorrect ones using conditional formatting then set the formula to

=IF(ValidPostCode(A1),0,1)

I can send you a spreadsheet with examples if you want.

[TheFaQQer]

Oh, that formula only works if the post code is split "correctly" into separate parts. Technically that's an optional in UK postal codes, so you may find that some of them don't give an "OK" or "Not OK" answer because they don't contain a space.

[LisaContractorUmbrella]

Hi TF

Basically I have a spreadsheet which will contain different numbers of rows of data each time it's used but it will contain same number of columns. One of the columns is for Postcodes and, as the data has been lifted from somewhere else, I need to confirm that they are in the correct format i.e. that they are genuine UK postcodes. I have tried the code with conditional formatting but it's not returning any errors and I know that there are errors in the list. if you could email examples that would help enormously - you guys on here are great - [email protected]

[mudskipper]

Thanks again - it's accepted (O2) but nothing seems to be happening - does it need to be 'run' in the same way as a macro or should it just do something

Use it in your spreadsheet like you would any other function.

Basically it returns true if the postcode is valid. So if in cell P2 you type =ValidPostcode(O2) you should see True or not. Personally I would rename it IsValidPostcode.

So you can then use it as part of a conditional statement or whatever you want to do.

[TheFaQQer]

Email sent