Using IF to find a value in an array.

[paul_kingtiger]

Hello,
I'm writing some VBA code for an Excel sheet.

What I want to do is run an IF statement to run some code. The IF has to be triggered if a value in a target cell is in an array.

So if the target cell value is in the array the code runs, if it isn't I use Else, EndIF to bypass the code.


I'm at a loss and would appreciate any help.

[CeeBee]

This will mark odd numbers smaller than 10, they are defined in the OddNumbers array

'--trigger a check on cell edit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    CheckNumber Target
End Sub

Private Sub CheckNumber(ByVal Target As Excel.Range)
    Dim OddNumbers As Variant
    Dim IsOdd As Boolean
    Dim i As Integer
    Dim CellVal As Integer
    OddNumbers = Array(1, 3, 5, 7, 9)
    CellVal = Val(Target.Value)
    i = LBound(OddNumbers)
    '--check if number is in array
    While i <= UBound(OddNumbers) And IsOdd = False
        If CellVal = OddNumbers(i) Then
            IsOdd = True
        End If
        i = i + 1
    Wend
    
    If IsOdd = True Then
        MarkNumberAsOdd Target
    End If
    
End Sub

Private Sub MarkNumberAsOdd(ByVal Target As Excel.Range)
    ActiveSheet.Cells(Target.Row, Target.Column + 1) = "is odd"
End Sub