read a range, perform calc, return result into cell

[brya6347]

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

 

[jindon]

Hi,

try to change

If ((X(K, 16) - i) ^ 2 + (Y(K, 17) - J) ^ 2) ^ 0.5 <= radiusspray Then

to

If ((X(K, 1) - i) ^ 2 + (Y(K, 1) - J) ^ 2) ^ 0.5 <= radiusspray Then

your array has size of x(1 to 10, 1 to 1), k(1 to 10, 1 to 1)

rgds,
jindon

 

[brya6347]

still getting a subcript error

wouldnt X(k,1) [supposing k = 2] read in cell A3? I am needing it to read in cell P2 ---> meaning X(k,16)...

i am trying a different method by using

Dim myrange as range
Set myrange = range("P2:Q11")

the only problem with this is that i am not sure how to run calculations on a mulit-dimensional range

for example how could I multiply P2*Q2 and put the result into cell B2?

 

[jindon]

Hi,

hope this will give you an idea

Sub test()
Dim myRange As Range, x() As Variant, i As Integer
With ActiveSheet
Set myRange = .Range("p2:q11")
x = myRange.Value
For i = LBound(x) To UBound(x)
    .Range("b" & i).Value = x(i, 1) * x(i, 2)
Next
End With
End Sub

jindon

 

[brya6347]

thanks, that is getting me headed into the right direction...

what is the "b" for in ".Range("b" & i).value?

 

[jindon]

brya6347,

"b" for .Range("b" & i) is just a column refference like Col.B & Row i

jindon