Returning contents of Last Used Cell in Column

I/O_Dork

Board Regular
Joined
Jul 15, 2004
Messages
75
Hi all:

I know how to write a formula to return the contents of the last used cell in a given column or range, but the issue here is that I don't just want to look for the actual contents of the last cell but the results of the formula in the last used cell.

For example, if I have a formula that occupies the cells in column R (or say out to row 500) and my last logical [true] formula result occurs on R10 not R500... I want the offset or index formula to return the last cell based on the formula result, not that fact that its the last cell that has anything in it or even a formula. How do I accomplish this. Currently, my offset formula is returning cell R500 b/c that is the last cell with content and not R189 which has a formula result of $99,000.00. The column is formated for number(accounting) as the formulas in column A return the dollar amount invested.

Here is my "find last used cell" formula:
=OFFSET(Blotter!$R$1,MATCH(MAX(Blotter!$R:$R)+1,Blotter!$R:$R,1)-1,0)


Any input is much appreciated!

Brian
 

Arul.rajesh

Active Member
Joined
Sep 20, 2011
Messages
285
Is your data in column "R" sorted?
why do you use an approximate match?
also why did you add 1 to the maxvalue in column R when you were doing an approximate match anyway?
Does column "R" contain a combination of formulas and constants?
Do you want to find the last row in column "R" which has the formula?
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,793
As I understand it, you have a formula (somewhere) and based on the results of that formula, you want to look at the "last cell based on that formula result".

Your request is not clear.

Given a column, are you looking for the last cell in that column that contains a formula?
In VBA, that would be
Code:
With Columns(myColumn).SpecialCells(xlCellTypeFormulas)
    With .Areas(.Areas.Count)
        MsgBox .Item(.Cells.Count).Address
    End With
End With
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top