Don't do a second find. Just use instr() and look for a case sensitive match.

If you find the lower case version, then do what you want, else do another find.

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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:=True)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'really look at formulas? Not .value???
If InStr(1, FoundCell.Formula, SecondWord, _
vbBinaryCompare) 0 Then
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If
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:

capitalization Find.

SearchString1 = Blackberry
SearchString2 = blackberry

Please Help. :-)

I need to Find two matches in a cell.
I need to find the first and second searchstring in a cell,
then move move down to the next cell and do it all over
again for the next cell in the column.

The problem is the second SearchString2 starts a find
all over again from the first word in the cell.

'====================================

Set rngToSearch = Sheets("Input data").Columns("G")
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
Set rngFound = rngToSearch.Find(What:=SearchString1, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
On Error GoTo 0
For Each c In rng
'find first SearchString1
For Each c in rng
Set rngFound = rngToSearch.Find(What:=SearchString2, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'find second SearchString2
Next c
Next c

'=================================

you help is very much appreciated

Jeff
--
Jeff

--

Dave Peterson

thanks Dave
I'll give it a try

:-)
--
Jeff


"Dave Peterson" wrote:

Don't do a second find. Just use instr() and look for a case sensitive match.

If you find the lower case version, then do what you want, else do another find.

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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:=True)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'really look at formulas? Not .value???
If InStr(1, FoundCell.Formula, SecondWord, _
vbBinaryCompare) 0 Then
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If
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:

capitalization Find.

SearchString1 = Blackberry
SearchString2 = blackberry

Please Help. :-)

I need to Find two matches in a cell.
I need to find the first and second searchstring in a cell,
then move move down to the next cell and do it all over
again for the next cell in the column.

The problem is the second SearchString2 starts a find
all over again from the first word in the cell.

'====================================

Set rngToSearch = Sheets("Input data").Columns("G")
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
Set rngFound = rngToSearch.Find(What:=SearchString1, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
On Error GoTo 0
For Each c In rng
'find first SearchString1
For Each c in rng
Set rngFound = rngToSearch.Find(What:=SearchString2, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'find second SearchString2
Next c
Next c

'=================================

you help is very much appreciated

Jeff
--
Jeff

--

Dave Peterson

My next question is to ask if you need to find BlAcKbErRy, too?

If you're trying to find all occurrences of blackberry without looking at case,
then it gets simpler. You don't need to look twice--you just need to change the
..find to ignore case (matchcase:=false).

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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) '<--- biggest change

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'do the work here
'or build a range of all the cells???

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

Depending on what you're doing, you could drop the second loop and just modify
the first to find the info and work on it.



Jeff wrote:

Hi Dave

My fault... I was not correct in this statement.
"I need to Find two matches in a cell."

I should have stated the following
"I need to Find either or both matches in a cell."

Thank you for helping...
Taking your time to write out this code is appreciated.
It's very clever...

Jeff :-)

--
Jeff

"Dave Peterson" wrote:

Don't do a second find. Just use instr() and look for a case sensitive match.

If you find the lower case version, then do what you want, else do another find.

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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:=True)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'really look at formulas? Not .value???
If InStr(1, FoundCell.Formula, SecondWord, _
vbBinaryCompare) 0 Then
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If
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:

capitalization Find.

SearchString1 = Blackberry
SearchString2 = blackberry

Please Help. :-)

I need to Find two matches in a cell.
I need to find the first and second searchstring in a cell,
then move move down to the next cell and do it all over
again for the next cell in the column.

The problem is the second SearchString2 starts a find
all over again from the first word in the cell.

'====================================

Set rngToSearch = Sheets("Input data").Columns("G")
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
Set rngFound = rngToSearch.Find(What:=SearchString1, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
On Error GoTo 0
For Each c In rng
'find first SearchString1
For Each c in rng
Set rngFound = rngToSearch.Find(What:=SearchString2, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'find second SearchString2
Next c
Next c

'=================================

you help is very much appreciated

Jeff
--
Jeff

--

Dave Peterson


--

Dave Peterson

Does this mean that you have your solution? I'm kind of confused.

If you need more help, what do you want to do when you find your string?



Jeff wrote:

Messed up in typing this out

'This is basically what I've did without using 'Find'
Figuring out how to use 'Find' can be a pain.
Var = "Blackberry"
SearchString = "blackberry"

Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))

For Each c In rng
Str1 = InStr(1, c.Value, Var)
Str2 = InStr(1, c.Value, SearchString)
If Str1 < 0 Or Str2 < 0 then
'if Str is not zero then
'return all infor in cell to a worksheet
end if
Next

--
Jeff

"Dave Peterson" wrote:

My next question is to ask if you need to find BlAcKbErRy, too?

If you're trying to find all occurrences of blackberry without looking at case,
then it gets simpler. You don't need to look twice--you just need to change the
..find to ignore case (matchcase:=false).

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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) '<--- biggest change

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'do the work here
'or build a range of all the cells???

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

Depending on what you're doing, you could drop the second loop and just modify
the first to find the info and work on it.



Jeff wrote:

Hi Dave

My fault... I was not correct in this statement.
"I need to Find two matches in a cell."

I should have stated the following
"I need to Find either or both matches in a cell."

Thank you for helping...
Taking your time to write out this code is appreciated.
It's very clever...

Jeff :-)

--
Jeff

"Dave Peterson" wrote:

Don't do a second find. Just use instr() and look for a case sensitive match.

If you find the lower case version, then do what you want, else do another find.

Option Explicit
Sub testme()

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

FirstWord = "Blackberry"
SecondWord = LCase(FirstWord) 'is this ok

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:=True)

Set FoundRng = Nothing
If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
'really look at formulas? Not .value???
If InStr(1, FoundCell.Formula, SecondWord, _
vbBinaryCompare) 0 Then
If FoundRng Is Nothing Then
Set FoundRng = FoundCell
Else
Set FoundRng = Union(FoundRng, FoundCell)
End If
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:

capitalization Find.

SearchString1 = Blackberry
SearchString2 = blackberry

Please Help. :-)

I need to Find two matches in a cell.
I need to find the first and second searchstring in a cell,
then move move down to the next cell and do it all over
again for the next cell in the column.

The problem is the second SearchString2 starts a find
all over again from the first word in the cell.

'====================================

Set rngToSearch = Sheets("Input data").Columns("G")
On Error Resume Next
Set rng = Range("G2", Cells(Rows.Count, "G").End(xlUp))
Set rngFound = rngToSearch.Find(What:=SearchString1, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
On Error GoTo 0
For Each c In rng
'find first SearchString1
For Each c in rng
Set rngFound = rngToSearch.Find(What:=SearchString2, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=True)
'find second SearchString2
Next c
Next c

'=================================

you help is very much appreciated

Jeff
--
Jeff

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

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

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

I'm still confused. I don't know why you're doing another find.

Jeff wrote:

Hi Dave

this infor should help clarify.
If the search finds any version of the word 'blackberry' in each cell of
the service description column, for example,
then the entire contents of that cell are inserted into worksheet Service
List.
The Service Catalog program searches for this word in all cells to build
a listing of services for the user.

For example if you searched for the word 'PC' or 'Pc' in the service
description
the program would update the service list sheet with any cells containing
that word.

in your code version...

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

I need to substitue 'myCell.Address(0, 0)' for 'SPosition'
, but SPosition is an integer that tracks positioning of each
cell in the Service Description column.
When a match is found in a cell, SPosition tracks that position and
it looks at SPosition 6 columns to the left at another column containing
the Header information for the found cell. This is basically what
my program accomplishes with Instr().
Using you Find method is far more cleaner and reliable.

my code

With Sheets("Service List")
.Range(MyName).Value = rng.Offset(SPosition, -6).Value
rng.FindNext
End With

Substituing 'SPosition' for 'myCell.Address(0, 0)' in your code

With Sheets("Service List")
.Range(MyName).Value = rng.Offset(myCell.Address(0, 0), -6).Value
rng.FindNext
End With

I hope this clarifies.
Your code version helped me alot to see other methods I can use
so it is well appreciated

--
Jeff

"Dave Peterson" wrote:

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


--

Dave Peterson

Glad you have something that works.

Jeff wrote:

Dave

Because of your help I got something that works using instr()
Sorry for the vague details.

Best wishes for the holidays

Jeff :-)

--
Jeff

"Dave Peterson" wrote:

I'm still confused. I don't know why you're doing another find.

Jeff wrote:

Hi Dave

this infor should help clarify.
If the search finds any version of the word 'blackberry' in each cell of
the service description column, for example,
then the entire contents of that cell are inserted into worksheet Service
List.
The Service Catalog program searches for this word in all cells to build
a listing of services for the user.

For example if you searched for the word 'PC' or 'Pc' in the service
description
the program would update the service list sheet with any cells containing
that word.

in your code version...

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

I need to substitue 'myCell.Address(0, 0)' for 'SPosition'
, but SPosition is an integer that tracks positioning of each
cell in the Service Description column.
When a match is found in a cell, SPosition tracks that position and
it looks at SPosition 6 columns to the left at another column containing
the Header information for the found cell. This is basically what
my program accomplishes with Instr().
Using you Find method is far more cleaner and reliable.

my code

With Sheets("Service List")
.Range(MyName).Value = rng.Offset(SPosition, -6).Value
rng.FindNext
End With

Substituing 'SPosition' for 'myCell.Address(0, 0)' in your code

With Sheets("Service List")
.Range(MyName).Value = rng.Offset(myCell.Address(0, 0), -6).Value
rng.FindNext
End With

I hope this clarifies.
Your code version helped me alot to see other methods I can use
so it is well appreciated

--
Jeff

"Dave Peterson" wrote:

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


--

Dave Peterson


--

Dave Peterson