none
VBA to check cell value against a list of valid values RRS feed

  • Question

  • Hello, all!

    In short, I have created a userform where employees enter their applied labor data (work order#, sales order#, employee#, good/bad parts made, etc.). The userform data gets copied to a worksheet in the same workboob and has checks (within the macro) run against the value within the cells, such as making sure the time spent is not < 0 or longer than their shift, that the length of the part# and sales orders are correct and so forth to make sure the data is very close to error-free, then the data gets copied into the master file/"database" and the original checkpoint worksheet gets cleared.

    I'm having trouble setting up a command to check the employee number against a list of valid numbers. The employee numbers are all 4 digits but they are not in tight sequence, so using a basic "< x and > y"-type if command won't work as there are still invalid employee numbers within that range.

    When there is an error found by my other checks, a msgbox pops up with the error when the try and click on the Save button in the userform (whose macro performs what I mentioned above)

    Is there a simple way to check a cell's value against a list of valid numbers?

    I appreciate the help!


    • Edited by smaier69 Friday, June 7, 2013 8:00 PM left out some function description
    Friday, June 7, 2013 7:40 PM

All replies

  • You could use VBA's Filter function.  For example:

    Sub IsItInTheList()
        ary = Array(34, 65, 111, 2, 77, 400, 15)
        Candidate = 2
        ary2 = Filter(ary, Candidate)
        MsgBox UBound(ary2) & vbCrLf & ary2(UBound(ary2))
    End Sub

    will display a Ubound of 0 and a value of 2

    But if Candidate is 17, Ubound will be -1, indicating 17 is not in the valid list.


    gsnu201304

    Friday, June 7, 2013 8:26 PM
    Moderator
  • How many employees? If a few hundred, copy to Excel and search. If thousands then look the number up in your database.

    Rod Gill

    The one and only Project VBA Book

    Rod Gill Project Management

    • Proposed as answer by KMickey Monday, June 10, 2013 3:40 PM
    • Unproposed as answer by KMickey Monday, June 10, 2013 3:40 PM
    Friday, June 7, 2013 10:17 PM
  • Gary's Student's solution is elegant; I've never encountered the Filter function before. 

    Just to provide a different approach, I thought I would float another solution:

    Sub InList(EmpID as Long)
        Dim EmpIDList as Range, ListEnd as long, RowCt as Long
            
        ListEnd = Sheets("EmpIDs").Range("A2").End(xlDown).Row
        
        Set EmpIDList = Sheets("EmpIDs").Range("A2:A" & ListEnd)
    
        For RowCt = 1 to EmpIDList.Rows.Count
            If EmpID = EmpIdList(,RowCt).Value Then Exit Sub
        Next Row
    
         MsgBox("Employee ID Number not Valid.")
    End Sub

    It seems likely the previously suggested "Filter" method may work faster, depending on the number of entries. 

    Saturday, June 8, 2013 3:18 PM
  • If you don't have too many employee #s then why not just put a combo box on the form and only let them select a valid employee # from the combo box?

    Leo Scott

    Saturday, June 8, 2013 6:05 PM
  • Hello Gary's Student. Thank you very much for the reply.

    As far as the code, I will be honest and say I only understand about half of what's going on in it as the Filter and UBound commands are something I have never used. I created a worksheet with the code and replaced Candidate = 2 with Candidate = Range("A1").Value to try and break/fix my way into understanding which works fairly well.

    One thing I noticed was partial entries would allow the code to still return the msgbox, such as 11 being seen as valid as it's a portion of the actual valid number 1111 or 5 as it is part of the valid numbers 65 and 15. Is there a way to prevent this?

    Also, when an invalid number is entered they macro breaks and I get a Run-time error '9' Subscript out of range error. I'm getting the gist of the code's mechanisms, but what could I add code-wise that would result in say, a msgbox saying "invalid emp#" rather than the number of occurrences  of the entry is found in the array?

    Monday, June 10, 2013 8:48 PM
  • Hello, Rod. I do appreciate the reply.

    Within the context of the userform and macro already being used, it's one employee at a time (but we have 30 employees who use this). The employee opens userform (workbook open event opens the userform), scans in/manually enter data and clicks the save button (activex control object that runs the macro), userform closes. There never is a time when they see the data they've entered other than what's in the userform. The macro does everything.

    However, you gave me an idea (or this was what you meant in the first place). Maybe using the Save Button's VBA to check what they entered against the "database"'s applied labor entries for known-to-be-good employee numbers. Only problem I could see is possibly time? The database workbook is thousands of rows long. I'll think about how to do it. On the surface I'm seeing it as "if the cell value can be found in database column x then carry on, otherwise msgbox with error and exit sub".(?)


    • Edited by smaier69 Monday, June 10, 2013 10:23 PM
    Monday, June 10, 2013 9:02 PM
  • Hello, KMickey. I thank you for the help.

    I just realized your code is what I was thinking of trying what I replied to Rod Gill above. I could just use this to scan through the master/database's employee number column (or a separate worksheet or workbook for this purpose) to see if there are occurrences, yes? As long as there was at least one instance in that column the labor data could be saved.

    We could break up the database file into quarterly reports is need be (90 days produces anywhere from 2000 to 5000 rows of data.

    Nice!

    Monday, June 10, 2013 9:09 PM
  • Hello, Leo. Thank you for the assistance on this.

    Honestly that was my first thought, but unfortunately we have very strict document control (ISO 9001, AS9100 compliance) and although document changes can be done they require formal revisions and such. If this turns out to be too tricky or cumbersome to solve with VBA, I'll do exactly what you recommended:)

    Monday, June 10, 2013 9:12 PM