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.
|