I have a VBA Code to calculate the distance between two cells with same values on a table. I just need the row difference between the cells which can be on different columns as seen in the picture. I need only distance on the "Y" axis, not on the "X" axis. This code has the functionality and design that I need but it calculates distance on "X" axis too.
In the example picture below, in column B, B5:Central matches with the closest (down) B12:Central, and the distance ( the number of rows between them) is 6. And in E1:250 it matches with the nearest G16:250, and the distance is 13.
The code I have is this:
Option Explicit Sub main() Dim cell As Range, f As Range Dim rowOffset As Long With Worksheets("gaps").Range("A2:F10") '<--| change this to your actual range of interest For Each cell In .SpecialCells(xlCellTypeConstants, xlNumbers) rowOffset = 1 Set f = .Find(what:=cell, after:=cell, LookIn:=xlValues, lookat:=xlWhole, searchdirection:=xlPrevious) If Not f Is Nothing And f.Row <= cell.Row Then rowOffset = cell.Row - f.Row + 1 cell.offset(, .Columns.Count + 1) = rowOffset '<--| the "+1" offset results range one column away from values range: adjust it as per your needs Next cell End With End Sub