# Excel VBA Check if a string contains given text.



## Lightingman2003

Hi.

I am wanting to know if its possible to write some VBA to check the contents of a Cell to see if it contains any of a given list of text. The given list would either be on a different sheet in the same workbook or hardcoded into VBA, preferably on a different sheet though.

If the cell doesnt match, then it would carry on and check one last thing before it transfers the whole row to a different sheet. This has already been coded and is working. 

Any help would be appreciated.

Thanks

Lightingman2003


----------



## Jimmy the Hand

Try this function


Code:


Function CheckText(CellToCheck As Range) As Boolean
    Dim CheckRange As Range, Hit As Range
    
    '***************************************************************    
    'definition of checkrange should be modified as needed
    [B]Set CheckRange = Workbooks("my workbook.xls").Sheets("CheckList").Range("A1:A100")[/B]
    '***************************************************************    
    Set Hit = CheckRange.Find(what:=CellToCheck.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
    CheckText = Not (Hit Is Nothing)
End Function

Usage:
There must be a range somewhere that contains the list of texts. The exact location of the range must be defined in the code line surrounded with ****** .
The input argument of the function is the cell that should be checked against the list.
The function returns *true* if the cell's value is found in the checklist, and *false* otherwise. The *LookAt* and *MatchCase* flags define whether the code looks for partial or whole text match and whether it differentiates between lower and upper case characters.

Hope this helps.

Jimmy


----------



## Lightingman2003

Thanks for that.

Just a quick question.

How will i call this Function? Is it the usual Call CheckText? 

Thanks once again!!

Lightingman2003


----------



## Jimmy the Hand

You can call it e.g. like this:



Code:


If CheckTest(Activesheet.Range("A1")) = True Then
    'the text in cell A1 is found, so do something with that
Else
    'the text in cell A1 is not found, thank the Gods
End If

Or you can use it in a spreadsheet formula, just like any built-in function. It's in the last category (which is called "user defined functions", or something similar).


----------

