OFFSET oddity

[Stuart MacDonald]

LastInColumn() is a VBA function I got from the web for finding the

last value in a colummn. It works, and returns a reference to the cell

instead of the value.

I want to look up the last value in P, move over to L and look up the

last value before that. I've figured out how to do this correctly. My

question is about the operation of the OFFSET function.

Two different ways to get the same range, or so I thought:

(1) =LastInColumn(OFFSET(L$1,0,0,ROW(LastInColumn(P$1:P30)),1))

(2) =LastInColumn(L$1:OFFSET(LastInColumn(P$1:P30),0,-4,1,1))

I have these formulas in multiple worksheets. (1) always works

properly. The problem is (2) has some very odd behaviour. What happens

is on the active worksheet (2) works correctly, but on the non-active

worksheets (2) updates immediately (Is OFFSET volatile? Seems to be.)

and gets a range that includes cells from the active worksheet! This

then causes the formula to get incorrect data.

This happens whether I use my function inside the OFFSET() call, or

simply hardcode say P23. Debugging my function shows that some cells

in the range come from the wrong worksheet.

So it definitely seems to be a problem with the L$1:OFFSET() notation.

I think this should work, but it obviously doesn't. Why not?

Of course, I started out with (2) because it avoids one level of

nesting. I was able to use (1) when I found CHOOSE() which flattened

out the nested IF() problem.

...Stu

[Jim Thomlinson]

A couple of thing... Yes Offset is Volatile. Secondly post your UDF. It
probably/possibly has references to activesheet (possible by default) which
it should not have.
--
HTH...

Jim Thomlinson

"Stuart MacDonald" wrote:

> ....Stu
>
>
>

[studog]

Jim Thomlinson wrote:
> A couple of thing... Yes Offset is Volatile. Secondly post your UDF. It
> probably/possibly has references to activesheet (possible by default) which
> it should not have.

It's a function I found on the web, but I have modified it a little:

Function LastInRange(InputRange As Range)
Dim CellCount As Long
Dim i As Long

CellCount = InputRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then
Set LastInRange = InputRange(i)
Exit Function
End If
Next i
LastInRange = ""
End Function

I don't see where an ActiveSheet reference would hide, but I'm newish
at VB/A.

...Stu

[Jim Thomlinson]

Your UDF does not reference the active sheet explicitly. UDF's however assume
teh active sheet unless otherwise specified. When a range is passed in the
sheet it came from is normally implied as ranges can only exist in one sheet.

When not specified otherwise ranges are assumed to originate in the sheet
where the formula is entered. In your example 2 when you essentially create
the range that you pass to the function you do not speicfy the sheet anywhere
so the UDF assumes the active sheet. The problem arises because you are
creating the range in in pieces. The sheet it originates from is lost.
--
HTH...

Jim Thomlinson

"studog" wrote:

> ....Stu
>
>

[studog]

Jim Thomlinson wrote:
> When not specified otherwise ranges are assumed to originate in the sheet
> where the formula is entered. In your example 2 when you essentially create
> the range that you pass to the function you do not speicfy the sheet anywhere
> so the UDF assumes the active sheet. The problem arises because you are
> creating the range in in pieces. The sheet it originates from is lost.

I think I understand:

OFFSET() is volatile, so it causes a recalc on the non-active sheet.
The range that is constructed on the non-active sheet is missing a
sheet specifier, and thus *assumes the active sheet, instead of the
sheet it resides on*?

That would be the source of my confusion; I assumed, sans specifier,
the range would refer to its home worksheet.

Is there a reason for making it work like that?

...Stu

[Jim Thomlinson]

Because you are making the range yourself you are overriding XL from making
what seems like a fairly simple decision. It is just an odd quirk of UDF's
(that to be quite honest I have never run into before but when I thought
about it it made sense)...
--
HTH...

Jim Thomlinson

"studog" wrote:

> ....Stu
>
>

[studog]

Jim Thomlinson wrote:
> Because you are making the range yourself you are overriding XL from making
> what seems like a fairly simple decision. It is just an odd quirk of UDF's
> (that to be quite honest I have never run into before but when I thought
> about it it made sense)...

So it seems to be a combination of the volatile worksheet
function, and the range construction then. Okay.

Thanks,
...Stu

[PapaDos]

Very interesting glitch !
And no easy way to fix it...

You should send a "bug report" to Microsoft...

Thanks.
--
Festina Lente

"studog" wrote:

> ....Stu
>
>