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 B26, 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)

 

[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

 

[deano]

Here is my original thread:

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 B26, 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 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]

http://groups.google.com/group/micr...030e190f30173b?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