This is the portion where you can do the work for each of those found cells:

For Each myCell In FoundRng.Cells
MsgBox myCell.Address(0, 0)
'or do what you want to each of them
Next myCell


But I don't understand what you're doing.

Jeff wrote:

Thanks Dave
Here is the rest of the code
for the Service Catalog program

Your search work great.
However...
This is the code I'm having difficulty in integrating into your program. :-)
.Range(MyName).Value = rng.Offset(SPosition, -6).Value

=========================
' SearchString passes the word enter by the user, like blackberry for
example

Sub Search_Copy(SearchString As Variant)
Dim c As Range
Dim RngToSearch As Range
Dim rngFound, rng As Range
Dim MyDesc, MyName, reset, SPosition, chng, SavPos, Str1, SearchString1
Dim Header
reset = 23 ' format positioning of cell data into service list
SPosition = 0 ' format positioning of cell data into service list
SearchString1 = " " & SearchString & " "
Application.ScreenUpdating = False
Worksheets("Service List").Range("C23:T1500").ClearContents
Worksheets("Service List").Range("E4").Value2 = " '" & SearchString1 & "'"
& " Search Results"
Sheets("Input data").Select
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
For Each c In rng
Str1 = InStr(1, c.Value, SearchString1)
If Str1 < 0 Then
' format cell header and info to be inserted into service list sheet
Sheets("Service List").Select
MyName = CVar(reset)
MyName = "C" & MyName
SavPos = MyName
MyDesc = CVar(reset + 2)
MyDesc = "C" & MyDesc
Range(MyDesc).Value = c.Value
With Sheets("Service List")
' update with all cell info if SearchString found (Str1 < 0)
in cell
.Range(MyName).Value = rng.Offset(SPosition, -6).Value
rng.FindNext
End With
reset = reset + 8
End If
SPosition = SPosition + 1
Next
=========================

--
Jeff

"Dave Peterson" wrote:

If you expect to have blackberry in every cell, then looping through the cells
seems ok.

But if you only had blackberry in one of the cells (say row 1 and row 23423),
then looping through all those cells looking for it would take a long time.

I still don't have any idea what the rest of your code does, but maybe this'll
help...

Option Explicit
Sub testme()

Dim FirstWord As String
Dim FirstAddress As String
Dim FoundCell As Range
Dim FoundRng As Range
Dim RngToSearch As Range
Dim myCell As Range

FirstWord = "Blackberry"

With Worksheets("Input data")
Set RngToSearch = .Range("G1", .Cells(.Rows.Count, "G").End(xlUp))
End With

With RngToSearch
Set FoundCell = .Cells.Find(what:=FirstWord, _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If

Set FoundCell = .FindNext(after:=FoundCell)

If FoundCell.Address = FirstAddress Then
Exit Do
End If
Loop
End If

If FoundRng Is Nothing Then
MsgBox "Not found!"
Else
For Each myCell In FoundRng.Cells
MsgBox myCell.Address(0, 0)
'or do what you want to each of them
Next myCell
End If
End With

End Sub


Jeff wrote:

Hi Dave
Thank you for staying with me on this.
I worked out a solution however please review...

I using InStr() to get the results I need...
When I find a string match in a cell I update a worksheet.
My program is a kind of crazy approach but it works.
It would be better to use 'Find' than this method using InStr().

Can you modify your program to "find all occurrences of blackberry"?
you mentioned it would be simplier to write.

Var = "Blackberry"
SearchString = "blackberry"
=======================
Sub Search_Copy(Var, SearchString As Variant)
Var4 = " " & Var & " "
SearchString4 = " " & SearchString & " "
Var3 = Var & " "
SearchString3 = SearchString & " "
Var2 = " " & Var
SearchString2 = " " & SearchString
For Each c In rng
Str1 = InStr(1, c.Value, Var)
Str2 = InStr(1, c.Value, SearchString)
Str3 = InStr(1, c.Value, Var2)
Str4 = InStr(1, c.Value, SearchString2)
Str5 = InStr(1, c.Value, Var3)
Str6 = InStr(1, c.Value, SearchString3)
Str7 = InStr(1, c.Value, Var4)
Str8 = InStr(1, c.Value, SearchString4)
If Str1 < 0 Or Str2 < 0 Or Str3 < 0 Or _
Str4 < 0 Or Str5 < 0 Or Str6 < 0 Or _
Str7 < 0 Or Str8 < 0 Then
Sheets("Service List").Select
MyName = CVar(reset)
MyName = "C" & MyName
SavPos = MyName
MyDesc = CVar(reset + 2)
MyDesc = "C" & MyDesc
Range(MyDesc).Value = c.Value
With Sheets("Service List")
.Range(MyName).Value = rng.Offset(SPosition, -6).Value
rng.FindNext
End With
reset = reset + 8
End If

=======================

--
Jeff

<<snipped


--

Dave Peterson