breezer123
New Member
 Joined
 Jul 13, 2020
 Messages
 18
 Office Version

 365
 Platform

 Windows
Hi all! New to Mr.Excel!
I am currently trying to run a code that will return all distance values between a number of x,y coordinates. i.e. the distance from each x,y location to all other x,y locations. Right now I have that as distance in the 2nd loop. I want to make this into an array, so that I can use those distance values inside of another macro. However, I can not figure out how to print the array or make it into one. I have tried the "dim distance(1 to 50, 1 to 50) as variant" to create a large matrix to then be filled up with my distance calculations in the loop because my matrix may be changing in size. Not sure if this is the correct approach. Could anyone help?
Here is the code I have.
Sub CalculateAllDistances()
Dim X1 As Double
Dim Y1 As Double
Dim X2 As Double
Dim Y2 As Double
Dim zon As Integer, wpz As Integer, rowcount As Integer, i As Integer, m As Integer
Dim distance(1 To 50, 1 To 50) As Variant
zon = Range("B1")
wpz = Range("B2")
rowcount = zon * wpz + 8
For i = 9 To rowcount
If Cells(i, 20).Value & Cells(i, 21) <> "" Then
X1 = Cells(i, 20).Value
Y1 = Cells(i, 21).Value
For m = i + 1 To rowcount
If Cells(m, 20).Value & Cells(m, 21).Value <> "" Then
X2 = Cells(m, 20).Value
Y2 = Cells(m, 21).Value
distance(i, m) = Sqr((Y2  Y1) ^ 2 + (X2  X1) ^ 2)
End If
Next m
End If
Next i
End Sub
I am currently trying to run a code that will return all distance values between a number of x,y coordinates. i.e. the distance from each x,y location to all other x,y locations. Right now I have that as distance in the 2nd loop. I want to make this into an array, so that I can use those distance values inside of another macro. However, I can not figure out how to print the array or make it into one. I have tried the "dim distance(1 to 50, 1 to 50) as variant" to create a large matrix to then be filled up with my distance calculations in the loop because my matrix may be changing in size. Not sure if this is the correct approach. Could anyone help?
Here is the code I have.
Sub CalculateAllDistances()
Dim X1 As Double
Dim Y1 As Double
Dim X2 As Double
Dim Y2 As Double
Dim zon As Integer, wpz As Integer, rowcount As Integer, i As Integer, m As Integer
Dim distance(1 To 50, 1 To 50) As Variant
zon = Range("B1")
wpz = Range("B2")
rowcount = zon * wpz + 8
For i = 9 To rowcount
If Cells(i, 20).Value & Cells(i, 21) <> "" Then
X1 = Cells(i, 20).Value
Y1 = Cells(i, 21).Value
For m = i + 1 To rowcount
If Cells(m, 20).Value & Cells(m, 21).Value <> "" Then
X2 = Cells(m, 20).Value
Y2 = Cells(m, 21).Value
distance(i, m) = Sqr((Y2  Y1) ^ 2 + (X2  X1) ^ 2)
End If
Next m
End If
Next i
End Sub