Need to get value of last number in a column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ron Smith - 23 Jan 2008 01:57 GMT

I submitted this an hour ago, but it was never posted -- and so I apologize
if it shows up twice. I have a column(s) of portfolio values, updated daily
or weekly. I want to find the last number entered in the column so that I
can compare it to the beginning of the year portfolio value and calculate my
losses for the year. I find I can use the ROW function to find the number of
the last Row that I entered, but I can't seem to use this number in a Cell
reference:
i.e. +C=ROW(A55) to reference Cell C55 doesn't work
Is there a way to make this function work in a Cell reference, or is there
another function that would give me this information or is there a better way
to do this.
Thanks.

Signature

Ron Smith in Round Rock

Reply to this Message

JLGWhiz - 23 Jan 2008 03:56 GMT

Not sure what you mean. Range("A55") is column A, row 55. Range("C55") is
column C, row 55. These can also be written as Cells(55, 1) and Cells(55, 3)
respectively. To assign them to a variable: myVar = Cells(55, 3) would make
myVar equal "$C$55". To find the last cell in a column that contains data
you can use: ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
That code snippet will cause VBA to look from the last row on the sheet in
column 3 (or C) until it finds a cell with data. That will be the last entry
in that column, so to use it in code you assign a variable like:

lastRow = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row

Then you can identify the cell as either Range("C" & lastRow) or
Cells(lastRow, 3)

> I submitted this an hour ago, but it was never posted -- and so I apologize
> if it shows up twice. I have a column(s) of portfolio values, updated daily
[quoted text clipped - 8 lines]
> to do this.
> Thanks.

Reply to this Message

Ron Smith - 24 Jan 2008 17:46 GMT

Sorry I didn't explain my problem that well. Here it is:

Line: Date Port 1 Port 2
Port 3
A B C D E F G H
I . . . . .
10
11 1/20 10 x x 20 x x 30
12 1/21 9 x x 22 x x 25
13 1/22 8 x x 24 x x 20
14 Last Line 1/23 7 x x 25 x x 15
15 (data added daily, or 5 rows at a time weekly)

20 12/31 14 15 40
21 1/23 7 25
15 <----

What I want to know is how to move the 1/23 values in Row 14, Columns B, C,
F, I, etc. down to Row 21. The rows after Row 13 are added sometimes daily,
but more often weekly at which time I add 5 rows at a time. I want to move
the values in the last line entered down to row 21, which will also move down
as I add more rows each day.

The "Last Line" is my invention. With the function ROW(A14), I can obtain
the number 14. So I wanted, for instance, to put the Cell Reference of
+C(=ROW(A14)), which would be "C14", into C21. I found that if I move the
"Last Line" text/cell in Column 1 with a "Cut" and Paste, the reference in
the ROW function will change to the last/current location of where Last Line
is pasted. So all I would have to do is move "Last Line" to the last row and
I am in business.

It all works out in my mind, but Excel doesn't see it my way. Can anyone
tell me what I am doing wrong, or how to get this to work, or give me a
simple solution on how to tackle this priblem.

Thanks.

Signature

Ron Smith in Round Rock

> Not sure what you mean. Range("A55") is column A, row 55. Range("C55") is
> column C, row 55. These can also be written as Cells(55, 1) and Cells(55, 3)
[quoted text clipped - 22 lines]
> > to do this.
> > Thanks.

Reply to this Message