Distance between cells

[Gary''s Student]

If the cells in a workshset are square (say 1 cm width and height) what is
the easiest way to calculate the distance between the center of one cell and
the center of another?

 

[Niek Otten]

Use Pythagoras: a^2+b^2 = c^2, where a and b are the distances in rows and
columns

 

[Ken Johnson]

Hi Gary's Student,
the following gave me an accurate result for the straight distance
between the centres of A1 and G7:

Public Sub Line_Length()
Dim Rng1 As Range, Rng2 As Range, c As Single, Side As Single
Set Rng1 = ActiveSheet.Range("A1")
Set Rng2 = ActiveSheet.Range("G7")
Side = Rng1.Height
c = Sqr(((Rng1.Row - Rng2.Row) * Side) ^ 2 + ((Rng1.Column -
Rng2.Column) * Side) ^ 2)
MsgBox c
End Sub

Ken Johnson

 

[Gary''s Student]

Niek & Ken:
Thank you both.

 

[L. Howard Kittle]

Hi Gary's Student,

I tried Ken's code and it gave me 108.1873. Is that centimeters or what? I
just tried it on a standard sheet, no cell resizing.

Curious as to what you are applying this to.

Regards,
Howard

 

[Ken Johnson]

Hi L. Howard Kittle,
measurements are in points (72 points per inch)
Ken Johnson

 

[Ken Johnson]

Hi Howard,
The code does rely on all cells being square, otherwise value of c is
incorrect.
I too am a little curious as to Gary''s Student's application.
Ken Johnson