Okay, that works, but what do I do with the value? It
simply returns the index of the cell, not the value of the
cell which is what I need. I did find a solution (see my
other post), but I'm still intrigued by this one and how
it could be used to get an easier solution.
Thanks!
Ben
P.S. FYI, on my other post, DO NOT put the Update() in the
Worksheet_Calculate routine as it will create an infinite
loop.
-----Original Message-----
You must enter these as array formula - as advised. To
do this enter the
formula, or if already entered press F2 to edit the
contents, then hold down
Ctrl and Shift and press Enter.
Cheers
Nigel
"Ben's Oak" wrote
in message
...
I created an example worksheet last night to give out to
other people, and tried the following two formulas in it
based on what you gave:
=MAX((NOT(ISBLANK(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
=MAX((NOT(ISNUMBER(Sheet1!D2:D65535))*ROW(Sheet1!
D2:D65535)))
Neither worked. The one using ISBLANK() reported 1 and
2 -
1 when I included D1 and 2 when I didn't. The one using
ISNUMBER() returned 0. The correct value should have
been
563.
Sheet 1:
A B C D
Description Withdrawl Deposit Balance
Initial $400.00 $400.00
$25.00 $375.00
$30.00 $405.00
$11.00 $394.00
$23.00 $371.00
$8.00 $363.00
$200.00 $563.00
$563.00
D1 = 400
D2 = D1 - B1 + C1
D3 = D2 - B2 + C2
Sheet2 is similiar.
And Sheet3 has the following format:
A B
Account Balance
Sheet1 $563.00
Sheet2 $683.00
Where column B is the last used cell from column D of
the
respective sheets. It is not necessarily the maximum
value
as the balance may go up and down based.
TIA,
Ben
-----Original Message-----
This will find the last cell in use in column B.
Enter this as an array formula (Ctrl + Shift + Enter):
=MAX((NOT(ISBLANK(B1:B65535))*ROW(B1:B65535)))
Or this one (again an array formula) will find the last
numeric cell:
=MAX((ISNUMBER(B1:B65535)*ROW(B1:B65535)))
For reasons best known to Microsoft, you can't define a
complete column to
make this method work, hence the formulas only go to
row
65535. Maybe
they'll change that in the next release!
--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
"Ben's Oak" wrote
in message
...
I have a workbook that has several worksheets in it,
once
of which is suppose to display the total balances
from
the
other worksheets. There are a total of 4 worksheets.
Worksheets 1 to 3 each have a column that has a
running
balance. (I.e. Cell_Value = Previous_Cell +
Additions -
Subtractions) These sheets will have an unknown
number
of
used cells as it will change each time data is
entered.
I'm using Excel 97 (can't afford to buy a new
version)
and
want to select the last used cell in the specified
column.
I can't seem to find a built in function, and am
having
some trouble getting Excel to find my user-defined
function. (Excel won't recognize it.)
Thus far, I've written:
Public Function Get_Last_Cell_Value( _
ByRef Current_Worksheet As Worksheet, _
ByRef Current_Column As String) As Currency
Dim Total_Count_Of_Column As Integer
Dim Column As Range
Set Column = Current_Worksheet.Columns(,
Current_Column)
Total_Count_Of_Column = Column.Count
Get_Last_Cell_Value = Column.Cells
(Total_Count_Of_Column)
End Function
which should do the trick. The function is stored
in "ThisWorkbook". It's been a while since I've done
VBA.
I don't usually program Excel. Am I missing
something?
Is
there an easier way to do this? I'd rather avoid
building
a macro if I can for security reasons.
Help much appreciated.
Ben
.
.