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:
http://pc-prog.eu/phpBB3/images/xMyKB/LastCell_Source_Book.xls

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)
    Else
        LastValueForName = ""
    End If
End With

End Function

Function MyLastCellInColumn(xRng As Range) As Variant

    Dim LastCell As Range, a As Range
    Set a = xRng
    Application.Volatile
       
    If IsEmpty(a.Cells(a.Cells.Count)) Then
        Set LastCell = a.Cells(a.Cells.Count).End(xlUp)
        If IsEmpty(LastCell) Then
          MyLastCellInColumn = ""
        Else
          MyLastCellInColumn = Cells(LastCell.Row, LastCell.Column).Value
        End If
    Else
        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.

Example:
http://pc-prog.eu/phpBB3/images/xMyKB/LastCell_Source_Book_v2.xls

With small code modification you can add new value after last cell.
See example 3 (VBA code - ALT+F11)
http://pc-prog.eu/phpBB3/images/xMyKB/LastCell_Source_Book_v3.xls

Palo

Palo,

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.

Iain


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:
http://pc-prog.eu/phpBB3/images/xMyKB/LastCell_Source_Book_v4.xls

Palo
Palo-admin
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ť

cron