-
Last entry in a Column
Every day, a new row of figures is entered below the last one.
On Row 2, I want to insert the value of the last entry in the column, i.e. today's figure.
So I entered into Cell S2 this formula:- =EndOfCol(S2)
Then I have this VBA Fucntion:-
Code:
Public Function EndOfCol(ThisCell As Range)
' Returns the value of the last cell in this column
Dim myCol, myCell, A, B, C, D
myCol = ColLett(ThisCell.Column) ' ColLett is a VBA Function that returns 1 or 2 letters given the Column Number
EndOfCol = Range(myCol & ThisCell.SpecialCells(xlLastCell).Row).End(xlUp).Value
End Function
... which, of course, doe not work - otherwise I wouldn't now be in this Forum ! ;-)
It actually "works", but returns the value in the Cell above, i.e. S1
It's the " .SpecialCells(xlLastCell). " bit that doesn't work.
Help !
-
Convoluted!
you don't need ColLett; test this:
Code:
Public Function EndOfCol(ThisCell As Range)
With ThisCell.Parent
EndOfCol = .Cells(.Rows.Count, ThisCell.Column).End(xlUp).Value
End With
End Function
I'm guessing there is no need only to look up from the sheet's xlLastcell row, but a small tweak if it transpires this is needed.
-
Quote:
Originally Posted by
p45cal
Convoluted!
test this:
Bravo ! Thank you very much indeed !
