PDA

View Full Version : Summing last two entries



N3K
07-24-2006, 08:41 PM
Sub SumLastTwo()

Lastrow = Range("A27").End(xlUp).Value

Lastrow2 = (Range("A27").End(xlUp).Row) - 1

Sum = Lastrow + Lastrow2

Range("B27").Value = Sum

End Sub

i don't know whether this has been asked before and solved or not but i don't have time to search one by one. I don't how to search for it also as i am very new to VBA.
i would like to sum the last two entries but i don't know how to get entry value for Lastrow2. Currently Lastrow2 return its address value. For example, i put value 10 in A26 and 15 in A25, i will get 10+25 = 35 instead of 10+15 = 25 and how to print the sum to the same row (row A27 -> B27)

lucas
07-24-2006, 09:05 PM
Try this, I'm not sure if its what your looking for....

Sub SumLastTwo()
Range("a65536").End(xlUp).Offset(0, 1).FormulaR1C1 = "=SUM(R[-1]C[-1],RC[-1])"
End Sub

fanpages
07-24-2006, 09:10 PM
Hi,

Try this:

Sub SubLastTwo()

[B27] = Cells([A27].End(xlUp).Row, "A") + Cells([A27].End(xlUp).Row - 1, "A")

End Sub

BFN,

fp.

jindon
07-24-2006, 09:15 PM
N3K,

Not clear the reason to be 35 instead of 25 when you enter 10 and 15

Shazam
07-25-2006, 06:51 AM
See if this would help you.

=SUM(TRANSPOSE(INDIRECT(ADDRESS(LARGE(ISNUMBER(A2:A100)*ROW(A2:A100),ROW(IN DIRECT("1:2"))),COLUMN(A2:A100)))))


This formula is an-array must hold down:

Ctrl,Shift,Enter

Example workbook below.

N3K
07-31-2006, 08:47 PM
i only manage to get the last entry with your codes. what i need is how to get the value for second last entry and i am doing VBA for excel.

Bob Phillips
08-01-2006, 01:25 AM
Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("B27").Value = Cells(iLastRow, "A").Value + Cells(iLastRow - 1, "A").Value

N3K
08-02-2006, 02:20 AM
Lastrow = Selection.Value
Range("D26").Value = Lastrow
Lastrow2 = Cells(Selection.Row - 1, Selection.Column).Value

Sum = Lastrow + Lastrow2
Range("D27").Select
Selection.Value = Sum

i solved the problem with this already. but i encountered another problem. how do i count the number of row with entries? can give me a code?