VBA look through sheets and pull back min date

Finalfight40

Board Regular
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

Well-known Member
Hi

Code:
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

Board Regular
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!
 

Some videos you may like

This Week's Hot Topics

Top