Find first row matching a criterion

[silviosilver]

Column A keeps a running total of certain events, day by day (row by row). When an event occurs on a day, the total is incremented. Whether an event will occur or not on a given day is unpredictable. Therefore some rows retain the same value as earlier rows.

A

4
5
6
6
7
7
7
8
9
9
9
10
11
12
13
14
15
15
15
15
16
17

I would like to know how many rows ago today's total (17, in the example) minus 10 occurred. In this example, 7 occurs three times, but it is immaterial whether I find the first instance of 7 or the last.

Can this be done?

 

[mumps]

I'm not sure what you mean by "how many rows ago today's total " but this macro will give you the row number:

Sub findRow()
    Application.ScreenUpdating = False
    Dim bottomA As Long
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim foundRow As Range
    Set foundRow = Range("A:A").Find(Cells(bottomA, 1) - 10, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundRow Is Nothing Then
        MsgBox foundRow.Row
    End If
    Application.ScreenUpdating = True
End Sub