union range problem

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

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.

--
Tim Williams
Palo Alto, CA


"Walter" <[email protected]> wrote in message news:[email protected]...
> 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

Got it. Thanks a lot!

"Tim Williams" wrote:

> 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.
>
> --
> Tim Williams
> Palo Alto, CA
>
>
> "Walter" <[email protected]> wrote in message news:[email protected]...
> > 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
>
>
>