Hi again,

Try setting your setting your BottomCell this way. I think that you will
have less problems. the offset takes it one cell past the last data cell and
therefore the After parameter will look at the actual last cell when Previous
is also set.

There are problems when trying to assign ranges in a single line statement.
The With/End With is the way to go.


With Worksheets(TabNames(i))
Set BottomCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

--
Regards,

OssieMac


"OssieMac" wrote:

Hi,

With Find the After parameter is exactly that "After". It starts looking in
the next cell. If you set After reference to the last cell then the actual
search starts at the first cell in the range being searched because it loop
around.

Hope this helps.


--
Regards,

OssieMac


"RocketMan" wrote:

So, I have a fuction that should find the top and bottom row in column
A that has term Cat(j). However, the top works all the time and the
bottom only works when I specifically set it to A65535, otherwise its
A1. HELP:

dim Cat() as string
dim TabNames() as string
dim TopCell as Range
dim BottomCell as Range

Cat = GetCategories
TabNames = GetTabNames

for i = 0 to UBound(TabNames)
Set TopCell = Worksheets(TabNames(i)).Range("A1")
Set BottomCell = Worksheets(TabNames(i)).Range("A65535")
With Worksheets(TabNames(i)).Range("A:A")
for j = 0 to UBound(Cat)
Set TopCell
= .Find(What:=Cat(j),After:=TopCell,LookIn:=xlValues ,SearchOrder:=xlByRows,SearchDirection:=xlNext)
Set BottomCell = .Find(What:=Cat(j),After:=
BottomCell,LookIn:=xlValues,SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
next j
Next I