VBA Advice

[purceld2]

Hi

I have found this bit of VBA code(Below) which does what I require, but when it finds the latest date I would like to retrieve a extra bit of data from the same line.

Could somebody point me in the right direction please?

Sub LastDate()
Dim Max_date As Date
Max_date = Application.WorksheetFunction.Max(Range("C3:C36"))
MsgBox Max_date
End Sub

 

[tonyyy]

You might consider the following...

Sub LastDate()
Dim Max_date As Date
Dim RowNo As Long

Max_date = Application.WorksheetFunction.Max(Range("C3:C36"))
RowNo = Application.Match(CLng(Max_date), ActiveSheet.Range("C1:C36"), 0)
MsgBox "Max Date: " & Max_date & vbCrLf & _
    "Row No: " & RowNo & vbCrLf & _
    Cells(RowNo, 4)  '<----Change column number to match your "extra bit of data"
End Sub

Cheers,

Tony

 

[offthelip]

The way i would do that is like this:

Sub test()
inarr = Range("c1:c36") ' Note I am starting the ragne at row 1 onot row 3 this is deliberate
max_date = 0
Maxi = 0
For i = 3 To UBound(inarr, 1) ' this starts the check on row 3
 If inarr(i, 1) > max_date Then
  max_date = inarr(i, 1)
  Maxi = i
 End If
Next i
ta = Range(Cells(Maxi, 1), Cells(Maxi, 1))
MsgBox (max_date & " this is on row " & Maxi)
MsgBox (" the value in column A on this row is " & ta)
End Sub