Find first row matching a criterion

silviosilver

New Member
Joined
Aug 12, 2015
Messages
24
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?
 
Last edited:

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,304
I'm not sure what you mean by "how many rows ago today's total " but this macro will give you the row number:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,983
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top