Last entry in a Column

[RobinClay]

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]

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]

Convoluted!
test this:

Bravo ! Thank you very much indeed !