Reports the distance between values with respect to a given value

[RIC63]

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

[Dave]

This seems to work. HTH. Dave

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

[Dave]

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

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

[Aussiebear]

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.

[Dave]

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

[Aussiebear]

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

[p45cal]

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

2024-05-29_155905.jpg

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:

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

=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?

[RIC63]

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
count.jpg

[RIC63]

.. with

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

now returns the desired values

thanks again everyone

[Aussiebear]

Why does Tcnt -1 need brackets?

[Aussiebear]

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.

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

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

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.

[Dave]

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

[Aussiebear]

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

[Dave]

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

[Aussiebear]

I was using the workbook provided in Post#1