Excel - locate the last entry

Common problems – different language?
Let's talk in English.

Excel - locate the last entry

Poslaťod Palo-admin » 26.10.2010, 09:28

Posted by Iain

I am trying to reference a last entry from a worksheet. Column A is the days of the year and row 1 are the names of people
- the table is populated with entries for people against date.
In a seperate workbbook I want to enter the date of the last entry for a given person. Many thanks.

To search last used cell you can use modification of function like this (Excel SpecialCells Method):

Kód: Vybrať všetko
Function MyLastCellAddress() As String
  Dim x As Range
  Set x = Workbooks("LastCell_Source_Book.xls").Worksheets("Sheet1").Cells.SpecialCells(11) 'xlLastCell = 11
  MyLastCellAddress = Replace(x.Address, "$", "", 1, -1, 1)
End Function

See example:

For the exact solution of your problem, you can use functions like these:

Kód: Vybrať všetko
Function LastValueForName(xRng As Range) As Variant
Dim x As Range, xName As String

xName = xRng.Text
With Worksheets("Sheet1").Range("C3:F3")
    Set c = .Find(xName, LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Set x = Columns(c.Column)
         LastValueForName = MyLastCellInColumn(x)
        LastValueForName = ""
    End If
End With

End Function

Function MyLastCellInColumn(xRng As Range) As Variant

    Dim LastCell As Range, a As Range
    Set a = xRng
    If IsEmpty(a.Cells(a.Cells.Count)) Then
        Set LastCell = a.Cells(a.Cells.Count).End(xlUp)
        If IsEmpty(LastCell) Then
          MyLastCellInColumn = ""
          MyLastCellInColumn = Cells(LastCell.Row, LastCell.Column).Value
        End If
        Set LastCell = a.Cells(a.Cells.Count)
        MyLastCellInColumn = Cells(IIf(xFirstRow = 0, LastCell.Row, xFirstRow), LastCell.Column).Value
    End If
End Function

Sheet and Range name you have to adjust according to your needs.


With small code modification you can add new value after last cell.
See example 3 (VBA code - ALT+F11)



Many thanks. I have just had alook at your suggestion and I have probably not described what I am looking for clearly. Your answer gives a blank return as the last entry but I am looking for the date of his last entry. Which in your example for George would be 5 Jan 2010 - when he had 3600 against his name. The same return for Paul should give 6 Jan 2010. Sorry if my question was confusing but you are on the right track.


Hi Iain,

OK, I understand. I made small modification of function "LastValueForName",
where one optional parameter is added (return column number). If omitted, value from found cell is returned:

=LastValueForName(I1, 2)

Try example 4:

Administrátor stránky
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť