[VBA] Check if an integer is contained in a range of cells in a worksheet

Hi,

I'm sure there is a very simple answer but I've been searching for 25 minutes and I've had no luck.

In my VBA code, I've got an integer i = 20 (for example).

In my worksheet, I've got a column of values (e.g. 4, 6, 12, 20, 22, 45)

I want to write an IF statement to see if i is in that column. If it is, I'll run <something>

If that fails, I want to check if i is in another column of values (e.g. 2, 3, 23, 46) and if it is, then I'll run <something>

Otherwise error.

Do I need a loop for this? Or can I compare the value to a 'range' of cells in a worksheet?

In code, what you are asking for looks like this;

' scan column 1
For MyRow = StartRow to EndRow

    ' get the value
    MyValue = Range("A"+Cstr(MyRow)).Value

    ' check the value
    If MyValue = i then

         ' run some code
         RunSomething

         ' exit the sub
         Exit Sub

    End If

Next MyRow

' scan column 2
For MyRow = StartRow to EndRow

    ' get the value
    MyValue = Range("B"+Cstr(MyRow)).Value

    ' check the value
    If MyValue = i then

         ' run some code
         RunSomething2

         ' exit the sub
         Exit Sub

    End If

Next MyRow

MsgBox "There was an error."