VBA look through sheets and pull back min date

[Finalfight40]

Hi All

I am looking for some help with a bit of VBA.

What i am looking to do is loop through the last few sheets (all but first 2, e.g. if i have 5 sheets it will look at the last 3, 6 sheets look at last 4 etc) and look at the row of the active cell and pull back the min date.

For example if on:

Sheet 3 the min date is 04/05/2018
Sheet 4 the min date is 30/05/2018
Sheet 5 the min date is 13/03/2018

the result in my active cell will be 13/03/2018 and ignore the others.

I will find the active cell by using the find function and the amount of sheets will be variable, also they will be named.

Thank you in advance for any help.

 

[Worf]

Hi

Sub AcrossSheets()
Dim r As Range, i%, v, res
res = 1E+77                 ' big number
For i = 3 To ThisWorkbook.Sheets.Count
    Sheets(i).Activate
    Set r = Intersect(ActiveSheet.UsedRange, ActiveCell.EntireRow)
    ' find minimum value on the row, excluding zeros
    v = Evaluate("=small(" & r.Address & ",countif(" & r.Address & ",0)+1)")
    If v < res Then res = v
Next
Sheets(1).[e15] = res       ' write result
End Sub

 

[Finalfight40]

Thank you Worf

This seems like a much cleaner way as opposed to the way i was approaching it.

I will store this with the list of other VBA tips i have learnt over the past couple months.

Much appreciated!