lastcell in thiscolumn function error

[deano]

Cheers all,
I wrote a UFD to return address of last cell of a column of data. The
function takes a cell reference in the column of interest as its
argument.

For example The data is in a range on worksheet1 B2:D6, lastcellref(b2)
should return $b$6

the Function code is:

Function lastcellref(thiscolumn As Variant)
Dim lastcell As Variant
Set lastcell = Cells(1001, thiscolumn.Column).End(xlUp)
Set lastcellref = lastcell.AddressLocal(RowAbsolute:=True,
columnabsolute:=True)
End Function

when I run this function, I get Run time error 424, object required.
what am I doing wrong?

[Bob Phillips]

Function lastcellref(thiscolumn As Variant)
Dim lastcell As Variant

Set lastcell = Cells(Rows.Count, thiscolumn.Column).End(xlUp)
lastcellref = lastcell.AddressLocal(True, True)
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

"deano" <avai...@availcompany.com> wrote in message
news:1132820908....@g43g2000cwa.googlegroups.com...

[Leith Ross]

Hello Deano,

Here is the amended code...

Function lastcellref(thiscolumn As Range)

Dim lastcell As Variant

With thiscolumn.Parent
lastcell = .Cells(.Rows.Count, Rng.Column).End(xlUp).Address(True,
True)
End With

lastcellref = lastcell

End Function

Sincerely,
Leith Ross

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18465
View this thread: http://www.excelforum.com/showthread.php?threadid=487877

[deano]

Here is my original thread:

should return $b$6

the Function code is:

Bob proposed that I modify code as below,

Function lastcellref(thiscolumn As Variant)
Dim lastcell As Variant

Set lastcell = Cells(Rows.Count, thiscolumn.Column).End(xlUp)
lastcellref = lastcell.AddressLocal(True, True)
End Function

Bob, when i run lastcellref(b2), I still get Run time error 424, object
required.

Leith propsed to midify it as below,

Function lastcellref(thiscolumn As Range)
Dim lastcell As Variant
With thiscolumn.Parent
lastcell = .Cells(.Rows.Count, Rng.Column).End(xlUp).Address(True,
True)
End With
lastcellref = lastcell
End Function

when I run lastcellref(b2), I get Complile error, Byref argument type
mismatch

Could you please take a closer look, thanks

[Bob Phillips]

Deano,

I know what is happeing. You are calling these functions from VBA, but you
are using Excel syntax. On a worksheet you could use =lastcellref(B2) and it
would work fine, but in VBA you have to use
myVar = lastcellref(Range("B2"))

--

HTH

RP
(remove nothere from the email address if mailing direct)

"deano" <avai...@availcompany.com> wrote in message

news:1132864238....@g14g2000cwa.googlegroups.com...

[deano]

http://groups.google.com/group/microsoft.public.excel.programming/browse_frm/thread/dab92fe1d7fe5bc0/7c030e190f30173b?lnk=arm&hl=en#7c030e190f30173b

Bob,

1. Yes The UDF works fine in the worksheet by using =lastcellref(B2)
but just like you said, you have to use ?lastcellref(Range("b2")) for
it to work in the immediate window. But why is that?

2. I tried the UDF in the worksheet using both my original version and
yours and they both work equally well with no "Run time error 424,
object required. " Why did it not work before ?

Function lastcellref(thiscolumn As Variant)
Dim thiscolumn As Variant

Set lastcell = Cells(1001, thiscolumn.Column).End(xlUp)
Set lastcellref = lastcell.AddressLocal(RowAbsolute:=True,

columnabsolute:=True) ' this is my original version
'Set lastcell = Cells(Rows.Count, thiscolumn.Column).End(xlUp) ' this
is Bob's version
End Function

thanks