Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

I have put a line into my calculation sub as follows:

Sheets("Calcs").select
ActiveSheet.Calculate

This seems to force my UDF to recalculate . . . I'm sure there is a more
elegant way to achieve the result though!

"TarrynG" wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

why use a UDF; just use the built in Lookup function.

=LOOKUP(9.99999999999999E+307,C:C)

--
Regards,
Tom Ogilvy


"TarrynG" wrote:

I have put a line into my calculation sub as follows:

Sheets("Calcs").select
ActiveSheet.Calculate

This seems to force my UDF to recalculate . . . I'm sure there is a more
elegant way to achieve the result though!

"TarrynG" wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

Maybe...

Option Explicit
Function LASTCELL(Input_Range As Range, n As Long) As Variant

Dim Col_Number As Long
Dim Last_Row As Long
Dim iRow As Long

Col_Number = Input_Range.Column

With Input_Range.Parent
Last_Row = -1

For iRow = .Cells(.Rows.Count, Col_Number).End(xlUp).Row To 1 Step -1
If Application.IsNumber(.Cells(iRow, Col_Number)) Then
Last_Row = iRow
Exit For
End If
Next iRow
If Last_Row < 0 Then
LASTCELL = "Not Found"
Else
'the adjusted last cell???
If (Last_Row - n) <= 0 _
Or (Last_Row - n) .Rows.Count Then
LASTCELL = "off the sheet!"
Else
LASTCELL = .Cells(Last_Row - n, Col_Number)
End If
End If
End With

End Function

And make sure you include enough in the function call so that excel knows when
to recalculate.

=lastcell(a:a,5)
for instance...

TarrynG wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

--

Dave Peterson

Thanks both Dave and Tom . . . I realise my UDF was not refreshing as like
you pointed out Dave, as I had not included enough in the function call.

However, Tom as usual has the cleanest solution! Thanks!

Tarryn

"Dave Peterson" wrote:

Maybe...

Option Explicit
Function LASTCELL(Input_Range As Range, n As Long) As Variant

Dim Col_Number As Long
Dim Last_Row As Long
Dim iRow As Long

Col_Number = Input_Range.Column

With Input_Range.Parent
Last_Row = -1

For iRow = .Cells(.Rows.Count, Col_Number).End(xlUp).Row To 1 Step -1
If Application.IsNumber(.Cells(iRow, Col_Number)) Then
Last_Row = iRow
Exit For
End If
Next iRow
If Last_Row < 0 Then
LASTCELL = "Not Found"
Else
'the adjusted last cell???
If (Last_Row - n) <= 0 _
Or (Last_Row - n) .Rows.Count Then
LASTCELL = "off the sheet!"
Else
LASTCELL = .Cells(Last_Row - n, Col_Number)
End If
End If
End With

End Function

And make sure you include enough in the function call so that excel knows when
to recalculate.

=lastcell(a:a,5)
for instance...

TarrynG wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

--

Dave Peterson

So you didn't want a UDF, huh?

TarrynG wrote:

Thanks both Dave and Tom . . . I realise my UDF was not refreshing as like
you pointed out Dave, as I had not included enough in the function call.

However, Tom as usual has the cleanest solution! Thanks!

Tarryn

"Dave Peterson" wrote:

Maybe...

Option Explicit
Function LASTCELL(Input_Range As Range, n As Long) As Variant

Dim Col_Number As Long
Dim Last_Row As Long
Dim iRow As Long

Col_Number = Input_Range.Column

With Input_Range.Parent
Last_Row = -1

For iRow = .Cells(.Rows.Count, Col_Number).End(xlUp).Row To 1 Step -1
If Application.IsNumber(.Cells(iRow, Col_Number)) Then
Last_Row = iRow
Exit For
End If
Next iRow
If Last_Row < 0 Then
LASTCELL = "Not Found"
Else
'the adjusted last cell???
If (Last_Row - n) <= 0 _
Or (Last_Row - n) .Rows.Count Then
LASTCELL = "off the sheet!"
Else
LASTCELL = .Cells(Last_Row - n, Col_Number)
End If
End If
End With

End Function

And make sure you include enough in the function call so that excel knows when
to recalculate.

=lastcell(a:a,5)
for instance...

TarrynG wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

--

Dave Peterson


--

Dave Peterson

Hey . . . actually in the end I did as I also needed to be able to find the
second last cell with a numeric value and the third last, etc, etc. Using the
UDF to extract the last 12 months of return data.

Thanks Dave

"Dave Peterson" wrote:

So you didn't want a UDF, huh?

TarrynG wrote:

Thanks both Dave and Tom . . . I realise my UDF was not refreshing as like
you pointed out Dave, as I had not included enough in the function call.

However, Tom as usual has the cleanest solution! Thanks!

Tarryn

"Dave Peterson" wrote:

Maybe...

Option Explicit
Function LASTCELL(Input_Range As Range, n As Long) As Variant

Dim Col_Number As Long
Dim Last_Row As Long
Dim iRow As Long

Col_Number = Input_Range.Column

With Input_Range.Parent
Last_Row = -1

For iRow = .Cells(.Rows.Count, Col_Number).End(xlUp).Row To 1 Step -1
If Application.IsNumber(.Cells(iRow, Col_Number)) Then
Last_Row = iRow
Exit For
End If
Next iRow
If Last_Row < 0 Then
LASTCELL = "Not Found"
Else
'the adjusted last cell???
If (Last_Row - n) <= 0 _
Or (Last_Row - n) .Rows.Count Then
LASTCELL = "off the sheet!"
Else
LASTCELL = .Cells(Last_Row - n, Col_Number)
End If
End If
End With

End Function

And make sure you include enough in the function call so that excel knows when
to recalculate.

=lastcell(a:a,5)
for instance...

TarrynG wrote:

Hello

I have a UDF to get the contents of the last cell in a column where those
contents are a number and not "". I first find the last cell which typically
will contain a formula but have it's value set to "" and then 'count back'
until I hit a cell which has a number.

My function works, but when I run a sub to refresh other calculations on my
sheet, my UDF does not refresh and #VALUE! occurs. Any help will be greatly
appreciated.

Thanks
Tarryn

My Function:

Function LASTCELL(input_range As Range, n As Integer)
Application.Volatile True

col_number = input_range.Column

last_row = Cells(Rows.Count, col_number).End(xlUp).Row

Do Until Application.WorksheetFunction.IsNumber(Cells(last_ row,
col_number).Value)

last_row = last_row - 1

Loop

LASTCELL = Cells(last_row - n, col_number)

--

Dave Peterson


--

Dave Peterson