PDA

View Full Version : Last entry in a Column



RobinClay
05-20-2016, 04:11 AM
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:-


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 !

p45cal
05-20-2016, 05:20 AM
Convoluted!
you don't need ColLett; test this:
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.

RobinClay
05-20-2016, 07:47 AM
Convoluted!
test this:


Bravo ! Thank you very much indeed !