union range problem

[Guest]

This function finds and selects cells containing the word "Project" (adapted
from post by Jim Tomlinson- Thanks!). The last 4 lines are just for
debugging. All the correct cells are selected as desired (10 cells out of
about 900. All good so far. When Excel selects the cells in the unionized
range, it selects the right cells, but the cell addresses do not match the
cells that are selected. For example, I can see taht the selected cells are
{B33, B42, B67, B73, B129, B149, B403...}, but the debug printout of cell
addresses is {B41, B42, B43, B44, ... B50, B51}. This is the first time I've
used Union(). What am I doing wrong?

Private Function AllPrjCells() As Range
Dim rngSearch As Range
Dim rngFound As Range
Dim rngFirstOccurance As Range
Dim rngPrj As Range
Dim i As Integer

Set rngSearch = Sheets(1).Range("B2:B900")
Set rngFound = rngSearch.Find(what:="Project", Lookat:=xlPart)

If Not rngFound Is Nothing Then
Set rngFirstOccurance = rngFound
Set rngPrj = rngFound
Do
Set rngFound = rngSearch.FindNext(rngFound)
Set rngPrj = Union(rngPrj, rngFound)
Loop Until rngFound.Address = rngFirstOccurance.Address
End If
Set AllPrjCells = rngPrj
AllPrjCells.Select
For i = 0 To AllPrjCells.Cells.Count
Debug.Print AllPrjCells.Cells(i).Address
Next
End Function

 

[Tim Williams]

You need to loop through all of the areas in the range, and then through each of the cells in each area.

The cells(i) approach doesn't work for multi-area ranges.

 

[Guest]

Got it. Thanks a lot!

You need to loop through all of the areas in the range, and then through each of the cells in each area.

The cells(i) approach doesn't work for multi-area ranges.