most efficient way to check cell data against 3 known strings

Ok, so I need to check a cell against three known strings for a match, and I've been trying to work out the most efficient way to do it.

I was thinking of using select case with a StrComp statement for each case, something like this:

Case StrComp(strCellData, str1stKnownString) = 0

but I don't actually need the program to do anything if any of the known strings is a match, so the only case that would have any code would be case else.

I imagine this would work, but it just seems kind of inefficient to have "empty" cases just to check for a match.

Edit: attached a small dummy worksheet. What I need is to add all the numbers in column C except those described as "DEPOSIT", "W/D" (withdrawal) or "BALANCE" in column B. The descriptions other than those three mentioned generally look the way I've illustrated, with numbers and special characters, and some may also have a couple letters of text as well.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Originally Posted by scudder12

but it just seems kind of inefficient to have "empty" cases just to check for a match.

I often do this.

Unless you are executing sode 1000s of times in a cycle, in my view, clarity is usually preferable to efficiency. "Clarity" usually equates to "less error prone"

You could perhaps store the 3 exception strings in an Array and conduct a Match, eg:

Dim vExceptions As Variant
Dim strCellData As String
vExceptions = Array("String1","String2","String3")
....
If IsError(Application.Match(strCellData,vExceptions,0)) Then
    'do something

End If

(assumptions made re: case sensitivity in the above though)

If you want an exact match (ie case sensitive) using this type of approach you could perhaps revise the Array such that

a) it includes a Null

b) is sorted a-z

at which point you could use an HLOOKUP type approach (w/ binary search) and compare resulting string to criteria, eg:

vExclusions = Array("","stringA","stringB","stringC")
....
If Application.WorksheetFunction.HLookup(strCellData, vExclusions, 1) <> strCellData Then

End If

(you would need to test for Null also of course)

I hope that helps.

EDIT:

In hindsight - if you wanted to avoid using the Null you could use an Exact Match and Application.HLookup with a CStr, eg:

vExclusions = Array("stringA","stringC","stringB")
....
If CStr(Application.Hlookup(strCellData,vExclusions,1,0)) <> strCellData Then
    'do something
End If