read a range, perform calc, return result into cell

brya6347

New Member
Joined
Feb 22, 2005
Messages
5
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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

New Member
Joined
Feb 22, 2005
Messages
5
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi,

hope this will give you an idea
Code:
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

New Member
Joined
Feb 22, 2005
Messages
5
thanks, that is getting me headed into the right direction...

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

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
brya6347,

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

jindon
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,925
Members
414,416
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top