Solved: Search entire cell, mark if found

[ulfal028]

I'm looking for a way to search each entire cell in a column for a specific word, and mark another cell with a 1 if found.

Sort of:

For i = 2 To LastRow
Search Range("J" & i) for "word"
If found, then Range("K" & i) = 1
Next i

Reference:
http://vbaexpress.com/forum/showthread.php?t=8504

[Bob Phillips]

Why not just use a formula

In K2

=--(ISNUMBER(FIND(J2,"word")))

and copy down

[ulfal028]

The FIND-formula is case sensitive, and the search will be conducted on temporary sheets so I prefer Vba.

[Bob Phillips]

SEARCH is not case sensitive

[ulfal028]

I just figured it out:

Dim Cel As Range
Dim RngSearch As Range
Dim i As Long

For i = 1 To 65536
Set RngSearch = Range("J" & i)
Set Cel = RngSearch.Find(What:="word", LookIn:=xlValues, _
LookAt:=xlPart, MatchCase:=False)
If Not Cel Is Nothing Then
Range("K" & i) = 1
End If
Next i

This way each cell which contains "word" will be assigned the number 1.

[mdmackillop]

Rather than check 65536 cells one at a time, search the whole column. Use Offset to add the values to the corresponding cells.


Option Explicit
Option Compare Text
Sub DoFind()
Dim c As Range, FirstAddress As String
With Range("I:I")
Set c = .Find("word")
If Not c Is Nothing Then
FirstAddress = c.Address
Do
c.Offset(, 1).Value = 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
End Sub