Checking to see if a number exists in a column

**smaier69** · 06-14-2013, 10:55 AM

Hello, all!

Long story short, I need to check if a number exists in a column in a separate worksheet. If it does, then continue running the rest of the macro. If not, toss up a msgbox and exit the sub.

Previously I tried doing it entirely within VBA (the array of numbers being contained within the macro) with variants of:

If Cells(1, 4).Value <> 1527 or 8873 or 7610 or 2224 or 6010 Then
          Msgbox prompt:=("Employee# does not exist")
          Range("A1:S1").ClearContents
     Else
Exit Sub

This wound up not working I assume because the value to be checked (Cells(1, 4)) against the list would have to equal or not equal each of the values in the list individually(?). Either that or I am just not phrasing it correctly which is entirely possible because my VBA skills are awful.

A gentleman here had suggested creating the list in a column on a worksheet in the same workbook and use that as the list to be checked against. Problem is I just cannot figure out how to phrase that properly either. I'm open to either approach but at this point VBA has me beat.

**Mordred** · 06-14-2013, 11:02 AM

I'm not sure if this will fix your problem but this is the proper way to go about the If statement.

If Cells(1, 4).Value <> 1527 Or Cells(1, 4).Value <> 8873 Or Cells(1, 4).Value <> 7610 Or Cells(1, 4).Value <> 2224 Or Cells(1, 4).Value <> 6010 Then
          MsgBox prompt:=("Employee# does not exist")
          Range("A1:S1").ClearContents
     Else
Exit Sub

**smaier69** · 06-14-2013, 12:08 PM

Hello, Mordred! Thank you for the reply.

I tried the code and it behaves like before where it's rejecting any value I put in Cells(1, 4). I tried using an Array and Filter in VBA but I could not figure out how to prevent a partial from being accepted (22 being seen as valid as it's part of one of the array values 2224).

I do appreciate your help on the syntax, though. Will keep me from pulling my hair out next time