# Thread: reports the distance between values with respect to a given value

1. ## 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

2. 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```

3. 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```

4. 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.

5. 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

6. 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

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

8. 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 -

count.jpg

9. .. with

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

now returns the desired values

thanks again everyone

10. Why does Tcnt -1 need brackets?

11. 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.

12. 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

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

14. 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

15. I was using the workbook provided in Post#1