Calculate the Offset of two selected cells

Question

I require that users populate a UserForm with two cells. (Let's say these are cells "D1" and "F2"). I would like to use this offset between these two cells in other parts of the code (after searching for a keyword). Bear in mind users can choose any two cells on the sheet.

Answer 1

Try this function, please.

It will return the difference between rows and columns. If you need an absolute value, just un-comment the code lines making the difference positive:

Private Function cellsOffsetRC(Cell1 As Range, Cell2 As Range) As Variant
   Dim calOff As Long, rowsOff As Long, arrOff(1) As Variant

   arrOff(0) = Cell2.Row - Cell1.Row
    'If arrOff(0) < 0 Then arrOff(0) = arrOff(0) * (-1)
   arrOff(1) = Cell2.Column - Cell1.Column
    'If arrOff(1) < 0 Then arrOff(1) = arrOff(1) * (-1)

    cellsOffsetRC = arrOff
End Function

It can be checked with a test Sub like this:

Sub testCellsOffsetRC()
  Dim arrOff As Variant
  arrOff = cellsOffsetRC(Range("D1"), Range("F2"))
  Debug.Print "Rows Offset: " & arrOff(0),"Columns Offset: " &  arrOff(1)
  Debug.Print Range("D1").Offset(arrOff(0), arrOff(1)).address
End Sub