About Us

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pulling Multiple Text Strings in a One Cell	RebLan	Excel Discussion (Misc queries)	4	April 19th 10 03:41 PM
search for multiple strings	Lea from CA[_2_]	Excel Discussion (Misc queries)	4	October 16th 09 10:20 PM
Search for multiple strings in a list (w/in 1 cell) w/ Advanced fi	Maher	Excel Discussion (Misc queries)	5	July 7th 08 06:02 PM
Search multiple strings Difficult to figure out	Jeff	Excel Programming	2	November 23rd 06 04:18 AM
How to make a cell recognize multiple text strings?	Tourcat	Excel Worksheet Functions	1	February 8th 05 08:29 PM

Menu

About Us