Find specific text in a string

[Samuel]

Hi!


I need to program a VBA fonction who will check in every cell of a
specific column to find out if the cells contains a specific string...

By exemple, I want to find out if the string "server" is in any cells
of a column.

I cannot use something like Cells(row,column).Value because the cells
can have something else like "back-up,server,test etc..."

It is also possible that no cells will have this string... I've tried
to use the
Find Method but it give me an error if no cell have the string...

A way to handle this error could also be usefull..

Can anyone help me about this???


Thanks!

Samuel Levesque

 

[Bob Phillips]

Use

Cells(row,column).Value Like "*Server*"

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JE McGimpsey]

One way:


Dim bWordExists As Boolean
bWordExists = Application.CountIf(Columns(1).Cells, "*server*") > 0
If bWordExists Then MsgBox "found ""server"""

Another:

Dim rFound As Range
Set rFound = Columns(1).Find( _
What:="server", _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=False)
If Not rFound Is Nothing Then _
MsgBox "Found ""server"" at " & rFound.Address

 

[JE McGimpsey]

Note that this won't find "server", since Like is case sensitive unless
Option Compare Text is included in the module.

If you have mixed cases, you could use

LCase(Cells(row, column).Text) Like "*server*"

 

[Guest]

This procedure will but the text "Server Found" in the cell directly to the
left of the cell value beingn evaluated.

Sub Server()

Dim lRow As Long
Dim lCol As Long
Dim strVal As String
Dim varArray As Variant

lRow = ActiveCell.Row
lCol = ActiveCell.Column

strVal = Cells(lRow, lCol).Value

Do Until strVal = ""
varArray = Split(strVal)
For Each Item In varArray
If Item = "Server" Then
Cells(lRow, lCol + 1).Value = "Server Found"
End If
Next
lRow = lRow + 1
strVal = Cells(lRow, lCol).Value
Loop