Reports the distance between values with respect to a given value

I ask for help with the following :


I would like the distance (number of rows) between the values in column I that are greater than or equal to the value entered in cell D1 to be returned in column B,

I'm working on excel 2021

thanks in advance for the support

This seems to work. HTH. Dave

Code:

---

Sub test()
    Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
    End With
    For Cnt = 1 To LastRow
        Tcnt = Tcnt + 1
        If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
            ws.Cells(Cnt, "B") = Tcnt
            Tcnt = 0
        End If
    Next Cnt
End Sub

---

It would probably be better to clear the "B" column for a reset. Dave

Code:

---

Sub Test2()
    Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
        .Range(.Cells(1, 2), .Cells(LastRow, 2)).ClearContents
    End With
    For Cnt = 1 To LastRow
        Tcnt = Tcnt + 1
        If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
            ws.Cells(Cnt, "B") = Tcnt
            Tcnt = 0
        End If
    Next Cnt
End Sub

---

Don't know Dave. Even after correcting the sheet name, it adds the row counts.

Sorry Dave, when I posted only your first post was there.

Hmmm.... not real sure what you mean @Aussiebear. Isn't that what iwas supposed to happen? Total number of rows in "I" that are >= D1 go to "B" row where the "I" value is found. Total resets after an "I" value is found. Maybe I misunderstood but my limited testing seem to work. Dave

I was under the impression that the OP wanted to count the rows between the specific values where ever they were found in the column I

In the attached a formula in cell O4, with hints for arguments:

Attachment 31610

This (in Excel 2021) is based on a named lambda formula. The name is Distances (see Name Manager in the Formulas tab of the ribbon), its formula is:

Code:

---

=LAMBDA(rng,LowerLimit,LET(a,FILTER(SEQUENCE(ROWS(rng)),rng>=LowerLimit),b,DROP(VSTACK({0},a),-1),a-b-1))

---

If you don't want to create a named formula you could use this formula directly on the sheet (cell M4) which is:

Code:

---

=LAMBDA(rng,LowerLimit,LET(a,FILTER(SEQUENCE(ROWS(rng)),rng>=LowerLimit),b,DROP(VSTACK({0},a),-1),a-b-1))(I1:I53,D1)

---

I know it's not in the same format that you asked for but could it be of any use?

Good morning and thanks to everyone


Dave's routine does what I wanted. I only notice that the returned count is not exactly the distance between one occurrence and the next - see photo with the value 20 placed in D1 -


thanks again for your support
Attachment 31612

.. with

Code:

---

ws.Cells(Cnt, "B") = (Tcnt-1)

---

now returns the desired values

thanks again everyone

Why does Tcnt -1 need brackets?

My concern here is that if we use Dave's second submitted code which the OP say works if we change Tcnt to (tcnt-1) in line 11.

Code:

---

Sub Test2()
    Dim ws As Worksheet, LastRow As Integer, Cnt As Integer, Tcnt As Integer
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
        .Range(.Cells(1, 2), .Cells(LastRow, 2)).ClearContents
    End With
    For Cnt = 1 To LastRow
        Tcnt = Tcnt + 1
        If ws.Cells(Cnt, "I") >= ws.Cells(1, "D") Then
          ws.Cells(Cnt, "B") = Tcnt
          Tcnt = 0
      End If
    Next Cnt
End Sub

---

In this code we are setting Tcnt to 1

Code:

---

Tcnt = Tcnt + 1

---

Why isn't Tcnt set to zero initially? If we follow the OP's suggestion of amending the following line to this

Code:

---

ws.Cells(Cnt, "B") = (Tcnt - 1)

---

You are setting the value back to Zero if it finds a value greater than or equal to the value in cell D1, which means it doesn't count the find.

Hi R161. Looking at your output, by my count, it seems like I was right. I1:14 gives you 4. I5 gives you 1. Nine rows add the row with the number that you were looking for gives you 10. 3 rows add the row that you were looking for gives you 4. Not sure why why tcnt -1 is needed? Dave

Dave, it seems the OP wanted the gap between the values so the results should be 3,10,9,3,2,12

See that's what puzzling. U misspelled the OP wanted 3,9,9,3,2,12. 10 was bad. Not sure about the 12... can't see that many rows. With the OP's rules as you suggest, it should be 3,9,9,3,1,1,2(that I can see). Tcnt can't be set to zero initially because there's always one row. As long as everybody's happy. :) Dave

I was using the workbook provided in Post#1