Inserting a function

[Darin Kramer]

Howdie,

I want to insert the function below into VBA.

I create a new module and paste it in, but how do I run it, or see the
results of it?

Thanks

Darin


Function fzLastValue(RangeId As Variant, Optional byColumn As Boolean =
True)
Dim cLast As Long
Dim oLast As Range

Application.Volatile

If byColumn Then
cLast = Cells(Rows.Count, RangeId).End(xlUp).Row
Set oLast = Range(RangeId & cLast)
Else
cLast = Cells(RangeId, Columns.Count).End(xlToLeft).Column
Set oLast = Cells(RangeId, cLast)
End If

fzLastValue = oLast.Value

End Function


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Bob Phillips]

Darin ,

Had to make a couple of changes to make it work for me, but you use with a
simple =fzLastValue(A1:B10) in a worksheet

Function fzLastValue(RangeId As Variant, Optional byColumn As Boolean =
True)
Dim cLast As Long
Dim oLast As Range

Application.Volatile

If byColumn Then
cLast = Cells(Rows.Count, RangeId.Column).End(xlUp).Row
Set oLast = Cells(cLast, RangeId.Column)
Else
cLast = Cells(RangeId.Row, Columns.Count).End(xlToLeft).Column
Set oLast = Cells(RangeId.Row, cLast)
End If

fzLastValue = oLast.Value

End Function



--

HTH

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

 

[Frank Kabel]

Hi
you have to enter this function for example in a cell. e.g.
=fzLastValue(2)
for column B

 

[Frank Kabel]

Hi
should be:
=fzLastValue(B1)

Hi
you have to enter this function for example in a cell. e.g.
=fzLastValue(2)
for column B

 

[Darin Kramer]

Unfortunately I get an #/Value error when pasting that into the workbook
VBA module, and then within the worksheet referncing a cell (say b11 =
fzlastvalue(A1:b10)

Ideas most welcome... Im just trying to select a block of text....(size
not always known..)

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[Darin Kramer]

Thats what I got in the cell fzLastValue(B1) and still no result??? Im
sure its something really simple...? if it works on yours is it possible
to send me the blank workbook?

Thanks

D


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[JulieD]

Hi Darin

did you do insert / module and paste the function in there - or did you
paste the function into "ThisWorkbook" - the former option works for me.

BTW i used Bob's version of the code.

Cheers
JulieD

 

[Darin Kramer]

Hi Julie,

If i put it in the former i get a #/Value error
IF I put it in the latter i get a #/Name error,

So whatever I do I seem to be stuck!!! no idea why.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[JulieD]

Hi Darin

i've emailed you a sample workbook (to the hotmail address) with the code in
it - seems to work okay for me ... does it work on your system?

Cheers
JulieD

 

[JulieD]

Hi Darin

try

Function fzLastValue(RangeId As Variant, Optional byColumn As Boolean =
True)
Dim cLast As Long
Dim dLast As Long
Application.Volatile

If byColumn Then
cLast = Cells(Rows.Count, RangeId.Column).End(xlUp).Row
dLast = Cells(cLast, Columns.Count).End(xlToLeft).Column
Else
cLast = Cells(Rows.Count, RangeId.Column).End(xlUp).Row
dLast = 1
End If

fzLastValue = Cells(cLast, dLast).Value

End Function

Cheers
julieD