selecting a collection of cells from .Find()
 

I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address < firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX directly
without having to arrive at targetCells first?

Thanks a lot.

selecting a collection of cells from .Find()
 

You don't need the collection in making the range of all found cells unless
you need it for other purposes.

where targetCells.Count can be in tens of thousands:

If by that you mean you expect to find tens of thousands of cells and select
all, the normal simple method (below) will take a very long time if the
found cells will comprise a large number of multiple areas, ie non adjacent
cells.

Dim rMultiFound As Range
'code
'insert following in your Do loop

If rMultiFound Is Nothing Then
Set rMultiFound = iCell
Else
Set rMultiFound = Union(rMultiFound, iCell)
End If

This isn't suitable for extremely large multi areas unless you're very
patient, to do it in a time frame user a user is accustomed to expect is not
trivial. Even with other methods I wouldn't want to make a 'selection' of
8000+ areas as it becomes unstable, and would abort before making it (which
also requires working out the number of areas before making it).

Regards,
Peter T


"rockhammer" wrote in message
...
I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of
thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address < firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX
directly
without having to arrive at targetCells first?

Thanks a lot.

selecting a collection of cells from .Find()
 

In addition, you have cells on multiple sheets, so you will never be able to
do xxx.select

--
Regards,
Tom Ogilvy

"rockhammer" wrote in message
...
I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of
thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address < firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX
directly
without having to arrive at targetCells first?

Thanks a lot.

selecting a collection of cells from .Find()
 

Thank you, Peter T. Your suggestion worked wonderfully.
I will watch the size of the selection, and will think of a way to make
smaller selections when i hit the performance issues you noted.


"Peter T" wrote:

You don't need the collection in making the range of all found cells unless
you need it for other purposes.

where targetCells.Count can be in tens of thousands:

If by that you mean you expect to find tens of thousands of cells and select
all, the normal simple method (below) will take a very long time if the
found cells will comprise a large number of multiple areas, ie non adjacent
cells.

Dim rMultiFound As Range
'code
'insert following in your Do loop

If rMultiFound Is Nothing Then
Set rMultiFound = iCell
Else
Set rMultiFound = Union(rMultiFound, iCell)
End If

This isn't suitable for extremely large multi areas unless you're very
patient, to do it in a time frame user a user is accustomed to expect is not
trivial. Even with other methods I wouldn't want to make a 'selection' of
8000+ areas as it becomes unstable, and would abort before making it (which
also requires working out the number of areas before making it).

Regards,
Peter T


"rockhammer" wrote in message
...
I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of
thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address < firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX
directly
without having to arrive at targetCells first?

Thanks a lot.

selecting a collection of cells from .Find()
 

Thanks, Tom. I will modify the code to do one sheet at a time.


"Tom Ogilvy" wrote:

In addition, you have cells on multiple sheets, so you will never be able to
do xxx.select

--
Regards,
Tom Ogilvy

"rockhammer" wrote in message
...
I have two questions based on the following code which gives me the
collection "targetCells" where targetCells.Count can be in tens of
thousands:

' start of code
Dim iSheet As Worksheet
Dim iRange As Range
Dim foundOne As Boolean
Dim firstCell As String
Set targetCells = New Collection
findValue = "xyz"
foundOne = False
For Each iSheet In ActiveWorkbook.Worksheets
Set iRange = iSheet.UsedRange
Set iCell = iRange.Find(findValue, LookIn:=xlFormulas, LookAt:=xlPart)
If Not iCell Is Nothing Then
firstCell = iCell.Address
Do
targetCells.Add iCell
foundOne = True
Set iCell = iRange.FindNext(iCell)
Loop While Not iCell Is Nothing And iCell.Address < firstCell
End If
Next
' end of code

Question #1:
How can I convert the collection "targetCells" from the following code to
XXX such that I can do XXX.Select, i.e. select these cells I've found?

Question #2:
Is there a way to modify the code above such that I can get to XXX
directly
without having to arrive at targetCells first?

Thanks a lot.