Excel last column cell value to use in calculations 1

[Queryman]

In the worksheet I have, I need to calculate on one column, but to calculate I need to use a value that is another cell on the same sheet, but this cell is not always on the same spot. It is always on a certain column, but the location varies with the data. I need to reference it, how do I find that cell and how do I code my formula. I found this on the web and this function retrieves the last cell. Help is appreciated. I would prefer to do this inside Excel rather than via VBA.


Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ws

' Find the last real row

LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

Set LastCell = ws.Cells(LastRow&, LastCol%)

End Function



Michael

 

[MarkBeck]

I believe i can help but i need some more information about the nature of the desired cell. What chages its location, does it move or is it simply a cell containing a result.

Mark

 

[xlbo]

This function, with a bit of tweaking can be used fine:

Function LastCell() As String
Dim LastRow As Long, LastCol As Integer

' Error-handling is here in case there is not any
' data in the worksheet

On Error Resume Next

With ActiveSheet

' Find the last real row

LastRow = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row

' Find the last real column

LastCol = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column

End With

' Finally, initialize a Range object variable for
' the last populated row.

LastCell = Cells(LastRow, LastCol).Address

End Function


You can then use this in a cell to obtain the value in the last cell
=indirect(Lastcell)

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Queryman]

Thanks for the replies. I can get the indirect function to work, but not for the LastCell as you have it, not sure what the problem is.

Also I just heard from the user, that the last cell they need to find is on every page, not every worksheet. In this example they have a row which has a total, the work "TOTAL" only appears in that spot on the worksheet and also happens to be the last cell in that page. They need to use the cell which is the last one in that page and get the total amount to divide another number.

Sorry for the additional complications and don't shoot the messenger.

Thanks



Michael

 

[xlbo]

"on every page, not every worksheet"
Sorry but that is nonsensical to me......
do you mean on every page in the print preview ???
Are you working in excel 'cos if so, each worksheet IS a "page" - by worksheet do you mean workBOOK - I really don't understand what the problem is - the function as I gave it returns a cell address
This cell address, when used with INDIRECT will get the VALUE in the last cell

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[plantj]

Does this help...

If the total is in the last cell in column H on Sheet22 the following formula will return it.

=OFFSET(Sheet22!$A$1,COUNTA(Sheet22!$A:$A)-1,7)

 

[Queryman]

Hi Geoff;

It is every page in the print preview, this user has a formatted report that prints on several pages. Each page has a last cell. Does that make sense?




Michael

 

[xlbo]

Well it makes sense but it's gonna give you a VERY hard time
Now let me recap and please tell me if I am correct in my assumptions so far...

1) The user has a workbook with a sheet on it which they wish to print out
2) This sheet prints on multiple pages
3) Each page has the text "TOTAL" in the last ROW on that particular page


A couple of questions:

1) How are we to determine WHAT to total - ie are there any indicators we can use to determine which figures to total up for each one of these "Page Totals"

2) Is the word "TOTAL" in a cell on its own ?? and wher ein relation to this "TOTAL" should the actual value be entered ??

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[Queryman]

Geoff,
I will get back to you on this.
Thanks,



Michael