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

Do you have a separate function for "GetTabNames" ? If not, then the Find
will only apply to the active sheet. However, you should be getting errors
before it gets to the Find statement.

"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

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

What exactly are you trying to do? The code that you have (for both find
routines) will return the cell found by the last iteration of the loop (j) of
the final sheet (I). All of the previous iterations will do nothing. While
they may very will find something they will always be overwitten by the next
find. If that final value of Cat(j) is not found then BottomCell will be
Nothing.

My best guess is that you want to find the first and last row matching any
of your items in Cat(j). If that is the case then reply back. We can use some
temp variables in your existing code to find those for you fairly quickly...
I sitll don't get what you are doing with the i loop however.
--
HTH...

Jim Thomlinson


"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

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