+ Reply to Thread
Results 1 to 9 of 9

Validation: Compare Fields in a spreadsheet with VBA

  1. #1
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Question Validation: Compare Fields in a spreadsheet with VBA

    Hi,

    Is it possible to compare 2 fields as follows.

    Check if columnA contains "Size2", if yes then Column D must equal UK 2 If incorrect give error box saying "Size Error" and stop code, if OK continue
    Check if columnA contains "Size3", if yes then Column D must equal UK 3 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size4", if yes then Column D must equal UK 4 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size5", if yes then Column D must equal UK 5 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size6", if yes then Column D must equal UK 6 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size7", if yes then Column D must equal UK 7 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size8", if yes then Column D must equal UK 8 If incorrect give error box saying "Size Error", and stop code, if OK continue
    Check if columnA contains "Size9", if yes then Column D must equal UK 9 If incorrect give error box saying "Size Error", and stop code, if OK continue with rest of code

    We need the macro to check all rows in the spreadsheet that contain data, if the row does not contain any of the above then it is OK to continue.

    Also to clarify the ColumnA will be something like "ATA1 BLACK SU#Size3" So it will contain Size3 and not be equal to Size3

    Any help with this would be really appreciated as I am stuck on this and it would make my Macro perfect for what we need as It will hopefully make listing size errors a thing of the past.

    Regards,

    Usman

  2. #2
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Perhaps like this

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Hi,

    Sorry that did not work for me. I used it exactly as is, as I don't understand the code.

    I made 1 mistake earlier, it needs to be checked in columnE and so I changed that.

    I have uploaded an example file of what would need to be checked to help clarify the requirements a bit. That file is perfect and so that should give no errors on that file.

    Your help is really appreciated.

    Regards,

    Usman
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: Validation: Compare Fields in a spreadsheet with VBA

    This works for me with your sample data

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Hi,

    Thanks for that. That is very good. I have tested it in every scenario I can think of, however it fails in 2 scenario.

    1) If ColumnE is Blank and ColumnA has a size then it will not notify of this.
    2) If the number is not the last character in the cell, and also it does not check it is says "UK 3". For example if columnE was just "3" it would pass that.

    This code would make my macro much better than it is currently, however because of the above problems it may still not ensure perfect data.

    Also I would try to personalise it myself to fix these problems but I do not understand any of it. Would you be able to explain a little bit how its working as I can not see the words "Size3" or "UK 3" anywhere

    Once again thank you for your time. Really appreciate it.

    Regards,

    Usman

  6. #6
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Also if data does pass the validation how can I call the next process. I want it call a macro named "CheckMaster".

    Regards,

    Usman

  7. #7
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Ignore the last part as that worked easily enough. Just the 2 weaknesses in the validation to address if possible.

    Current code is:

    Sub Sizes()
    Sheets("Sheet3").Select
    Dim LR As Long, i As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    If Range("E" & i).Value <> "" Then
    If Right(Range("A" & i).Value, 1) <> Right(Range("E" & i).Value, 1) Then
    MsgBox "Mismatch on row " & i, vbExclamation
    Exit Sub
    End If
    End If
    Next i
    CheckMaster
    End Sub

  8. #8
    Forum Contributor
    Join Date
    06-07-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Give this a try

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    06-09-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    92

    Re: Validation: Compare Fields in a spreadsheet with VBA

    Thanks. That is great. It still has the problem that if the ColumnA does not end in the size then gives a false fail.

    However I believe the chances of this are next to 0 and therefore I think this is perfect.

    Thank you very much for the help. I really appreciate it. Hopefully we can use this go good effect. I will be studying VBA in much more detail to learn the possibilities as it seems it can do a lot more than I am currently using it for.

    Regards,

    Usman

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1