i found this code and am trying to adjust for my needs but it keeps stopping
at the indicated line.

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")

stops here
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)


If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub

Hi Jhyatt,

I think that the only Lookin parameters are xlValues, xlFormulas and
xlComments.

Just as an added extra it is recommended that you set all the
arguments/parameters for find because they are saved from the previous find
even if it was done in the interactive mode.

Regards,

OssieMac

"jhyatt" wrote:

i found this code and am trying to adjust for my needs but it keeps stopping
at the indicated line.

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")

stops here
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)


If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub

Hi again Jhyatt,

I decided to actually test your code and if you replace your find with the
following it works:-

Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Regards,

OssieMac


"OssieMac" wrote:

Hi Jhyatt,

I think that the only Lookin parameters are xlValues, xlFormulas and
xlComments.

Just as an added extra it is recommended that you set all the
arguments/parameters for find because they are saved from the previous find
even if it was done in the interactive mode.

Regards,

OssieMac

"jhyatt" wrote:

i found this code and am trying to adjust for my needs but it keeps stopping
at the indicated line.

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")

stops here
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)


If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub

Sorry it took so long to get back to you i changed the code as you suggested
and it works great. is there a way to have it look in multiple work sheets.

"OssieMac" wrote:

Hi again Jhyatt,

I decided to actually test your code and if you replace your find with the
following it works:-

Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
Regards,

OssieMac


"OssieMac" wrote:

Hi Jhyatt,

I think that the only Lookin parameters are xlValues, xlFormulas and
xlComments.

Just as an added extra it is recommended that you set all the
arguments/parameters for find because they are saved from the previous find
even if it was done in the interactive mode.

Regards,

OssieMac

"jhyatt" wrote:

i found this code and am trying to adjust for my needs but it keeps stopping
at the indicated line.

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")

stops here
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)


If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub

Hi again Jhyatt,

I don't think that you can set a union on multiple sheets or at least I
don't know how. What I have done is modify the code to step through all the
worksheets and set the interior color of the found cells to Yellow just to
give you an example of stepping through the worksheets.

Further to what JLG Whiz said, I am intrigued as to why you want to set a
union of the found cells or is this just a training example?

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim ws As Worksheet
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

'Repeat for each worksheet in workbook
For Each wks In Worksheets
Set rngToSearch = wks.Columns("A")

Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
'Set the interior color of found cell to Yellow
rngFound.Interior.ColorIndex = 6
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
Next wks

End Sub


Regards,

OssieMac

sorry Ossiemac had go home and sleep.

my goal is to find all records in the workbook with the word trade (trades
of service)in them to put them on a separate sheet so we can track the trades
in our business. I have been trying different codes to figure out a way to
do this and this one seemed to have potential.

"OssieMac" wrote:

Hi again Jhyatt,

I don't think that you can set a union on multiple sheets or at least I
don't know how. What I have done is modify the code to step through all the
worksheets and set the interior color of the found cells to Yellow just to
give you an example of stepping through the worksheets.

Further to what JLG Whiz said, I am intrigued as to why you want to set a
union of the found cells or is this just a training example?

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim ws As Worksheet
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim strFirstAddress As String

'Repeat for each worksheet in workbook
For Each wks In Worksheets
Set rngToSearch = wks.Columns("A")

Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If Not rngFound Is Nothing Then
strFirstAddress = rngFound.Address
Do
'Set the interior color of found cell to Yellow
rngFound.Interior.ColorIndex = 6
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
End If
Next wks

End Sub


Regards,

OssieMac

You should abandon this code and start over. It does nothing to nothing.
However,
It is stopping at the Set....Find statement because it cannot find the value
you define in the place you tell it to look...xlConstants. If you change
that to xlValues, then it should complete macro.

"jhyatt" wrote:

i found this code and am trying to adjust for my needs but it keeps stopping
at the indicated line.

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")

stops here
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)


If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub