• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

I have a stupid question

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    I have a stupid question

    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?
    Connect with me on LinkedIn

    Follow us on Twitter.

    ContractorUK Best Forum Advisor 2015

    #2
    Originally posted by LisaContractorUmbrella View Post
    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)
    Last edited by mudskipper; 23 October 2014, 11:10. Reason: better comparison example

    Comment


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

      Follow us on Twitter.

      ContractorUK Best Forum Advisor 2015

      Comment


        #4
        Originally posted by LisaContractorUmbrella View Post
        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.

        Comment


          #5
          Originally posted by mudskipper View Post
          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
          Connect with me on LinkedIn

          Follow us on Twitter.

          ContractorUK Best Forum Advisor 2015

          Comment


            #6
            Originally posted by LisaContractorUmbrella View Post
            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
            Code:
            =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,
            Code:
            =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
            Code:
            =IF(ValidPostCode(A1),0,1)
            I can send you a spreadsheet with examples if you want.
            Best Forum Advisor 2014
            Work in the public sector? You can read my FAQ here
            Click here to get 15% off your first year's IPSE membership

            Comment


              #7
              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.
              Best Forum Advisor 2014
              Work in the public sector? You can read my FAQ here
              Click here to get 15% off your first year's IPSE membership

              Comment


                #8
                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]
                Connect with me on LinkedIn

                Follow us on Twitter.

                ContractorUK Best Forum Advisor 2015

                Comment


                  #9
                  Originally posted by LisaContractorUmbrella View Post
                  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.

                  Comment


                    #10
                    Email sent
                    Best Forum Advisor 2014
                    Work in the public sector? You can read my FAQ here
                    Click here to get 15% off your first year's IPSE membership

                    Comment

                    Working...
                    X