here is the problem..i am a bit rusty on my VBA so please bear with me...
i have a range (P2:Q11)...it represents a 2 x 10 range with each row of the range as a x,y data point..
i.e. P2 = 2, Q2 = 3 represents the data point (2,3)..10 total data points
i need to write the code that will perform a calculation on all the 10 data points and put the result into each cell of a 10 X 10 grid (B2:L12)
to be more specific, it is for an engineering application that will determine how many times a point on the grid will fall in the vicinity of the 10 afforementioned data points given a certain radius around each data point...
Sub TotalShorter()
Dim X As Variant, Y As Variant
Dim z As Single
Dim i As Integer, total As Integer
radiusspray = 1.5
' read the x and y ranges into the variants
X = Range(Cells(2, 16), Cells(11, 16))
Y = Range(Cells(2, 17), Cells(11, 17))
total = 0
For i = 0 To 10
For J = 0 To 10
For K = 2 To 16
If ((X(K, 16) - i) ^ 2 + (Y(K, 17) - J) ^ 2) ^ 0.5 <= radiusspray Then
Cells(J + 2, i + 2) = total + 1
Else
Cells(J + 2, i + 2) = total
End If
Next K
Next J
Next i
End Sub
i am getting an error 9 - subscript out of range
i have a range (P2:Q11)...it represents a 2 x 10 range with each row of the range as a x,y data point..
i.e. P2 = 2, Q2 = 3 represents the data point (2,3)..10 total data points
i need to write the code that will perform a calculation on all the 10 data points and put the result into each cell of a 10 X 10 grid (B2:L12)
to be more specific, it is for an engineering application that will determine how many times a point on the grid will fall in the vicinity of the 10 afforementioned data points given a certain radius around each data point...
Sub TotalShorter()
Dim X As Variant, Y As Variant
Dim z As Single
Dim i As Integer, total As Integer
radiusspray = 1.5
' read the x and y ranges into the variants
X = Range(Cells(2, 16), Cells(11, 16))
Y = Range(Cells(2, 17), Cells(11, 17))
total = 0
For i = 0 To 10
For J = 0 To 10
For K = 2 To 16
If ((X(K, 16) - i) ^ 2 + (Y(K, 17) - J) ^ 2) ^ 0.5 <= radiusspray Then
Cells(J + 2, i + 2) = total + 1
Else
Cells(J + 2, i + 2) = total
End If
Next K
Next J
Next i
End Sub
i am getting an error 9 - subscript out of range