Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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) "Darin Kramer" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
Hi
you have to enter this function for example in a cell. e.g. =fzLastValue(2) for column B "Darin Kramer" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
Hi
should be: =fzLastValue(B1) "Frank Kabel" wrote: Hi you have to enter this function for example in a cell. e.g. =fzLastValue(2) for column B "Darin Kramer" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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" wrote in message ... 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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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 "Darin Kramer" wrote in message ... 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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inserting a function
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 "JulieD" wrote in message ... 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 "Darin Kramer" wrote in message ... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting dates function | Excel Worksheet Functions | |||
Inserting a function into a worksheet | Excel Discussion (Misc queries) | |||
Inserting Pictures with IF function | Excel Worksheet Functions | |||
Inserting an AutoDate Function | New Users to Excel | |||
Inserting a SUM Function | Excel Programming |