VBA to throw an warning if a column contains a value not on an "acceptable list"

[jardenp]

The cells in column O can only contain certain values. There is a header in O1 and I'd like to leave that open to changes. So, cells O2:O1000 can be 1, 01, 16, 23, 25, or blank (the "01" would be entered in the sheet as '01 so the zero displays). If they are not one of those values, I would like variable ClassCheck (type Variant) to be set to "N". If there are no cells in column O that are not on that list, I would like ClassCheck to be set to "Y".

I do not want to reference a table of acceptable values--I'd like those values to be in the code.

For example:

Row	Column O
1	ColHeader
2
3	01
4	23
5	1
6

<tbody>
</tbody>

Would set ClassCheck to "Y"

Row	Column O
1	ColHeader
2
3	17
4	23
5	001
6

<tbody>
</tbody>

Would set ClassCheck to "N" because O3's value is "17" and O5's value is "001", neither of which are on the acceptable list.

Thank you!

Josh in IN

 

[jardenp]

So I kept working at this and came up with something that appears to work. Like all my code, it's not graceful.

    'Check if Class Override values are not on acceptable list    Dim i As Integer
    Dim ClassCheck As Variant
    ClassCheck = "Y"
    For i = 2 To LRow
        If Cells(i, 15).Value = "" Or Cells(i, 15).Formula = "01" Or Cells(i, 15).Formula = "1" Or Cells(i, 15).Formula = "16" Or Cells(i, 15).Formula = "23" Or Cells(i, 15).Formula = "25" Then
        Else
            ClassCheck = "N"
        End If
    Next i
    
    If ClassCheck = "N" Then
        MsgBox "Class Override column contains a value that is not (blank), 01, 1, 16, 23, or 25. Please correct and rerun macro."
        Exit Sub
    End If

 

[JoeMo]

You can add some code where indicated by comments to do whatever you intend when ClassCheck is "Y" or "N".

Sub ClassCheck()
Dim V, i As Long, j As Long, ClassCheck, Acceptable, ct As Long
Acceptable = Array(1, "01", 16, 23, 25, "")
V = Range("O2:O100").Value
For i = LBound(V, 1) To UBound(V, 1)
    ct = 0
    For j = LBound(Acceptable) To UBound(Acceptable)
        If V(i, 1) <> Acceptable(j) Then
            ct = ct + 1
            If ct = UBound(Acceptable) + 1 Then
                ClassCheck = "N"
                'do something here
                Exit Sub
            End If
        Else
            Exit For
        End If
    Next j
Next i
ClassCheck = "Y"
'do something else here
End Sub

 

[jardenp]

Thanks, JoeMo! That's much more robust than my work around. I appreciate it and I can think of a couple other applications where your code will helpful.

 

[JLGWhiz]

Similar to JoeMo

Sub compare1()
Dim nm As Variant, c As Range, i As Long, ClassCheck As Variant
nm = Array(1, "01", 16, 23, 25)
With ActiveSheet
    For Each c In .Range("O2", .Cells(Rows.Count, 15).End(xlUp))
        If c <> "" Then
            For i = LBound(nm) To UBound(nm)
                If nm(i) = c.Value Then
                    x = x + 1
                End If
            Next
            If x > 0 Then
                ClassCheck = "Y"
            Else
                ClassCheck = "N"
            End If
        End If
        MsgBox ClassCheck
        x = 0
    Next
End With
End Sub

 

[JoeMo]

Thanks, JoeMo! That's much more robust than my work around. I appreciate it and I can think of a couple other applications where your code will helpful.

You are welcome - thanks for the reply.

 

[jardenp]

Thanks, JLG--also helpful and educational to a novice like me!

 

[JLGWhiz]

Thanks, JLG--also helpful and educational to a novice like me!

Quite welcome,
Regards, JLG