reading data from a range into a cell

i have a range of data (points on a 10 X 10 grid) that i need to read into a cell...

i posted a thread a bit ago but was unclear as to what it is I actually need help with

my range of data consists of 2 cloumns by 10 rows (10 data points)

example

x y
2 3
1 2
3 5
4 5
. .
. .
. .
. .
7 7

the target cell will be a basic count program that counts how many of the 10 data points are within a certain distance from the target cell

my thoughts are

(target cell - origin of 10 X 10 grid)

i = 1 (1st out of 10 data points)
count = 0
if distance from target cell to data point (i=1) <= radius then, count = count +1, else count = count
next i (until 10 data points are all read)

my programming is very rusty but i am trying to get a count of how many of the ten data points (i= 1 thru 10) are within the specified distance from the target cell (target cells are grid points)

for example if 4 of the 10 data points are within a certain distance, the target cell should display "4"

any help would be greatly appreciated

This should do what you want based on your description. It assumes that
your values are in columns A and B on the active sheet, and the total
is then written to cell C1.

GetTotal() prompts for the distance and passes it to the function
TotalShorter(), which returns the total. You could make it more
flexible by assigning range names ("X" and "Y"), or by adding prompting
for the ranges (either addresses or rows and columns).

Option Explicit

Sub GetTotal()
Dim d As Single
Do
d = Application.InputBox(Prompt:="Enter a value between 0 and 10:",
Type:=1)
Loop While d < 0 Or d > 10
If d = False Then
Exit Sub
Else
Cells(1, 3).Value = TotalShorter(d)
End If
End Sub

Function TotalShorter(distance As Single) As Integer
Dim X As Variant, Y As Variant
Dim z As Single
Dim i As Integer, total As Integer

' read the x and y ranges into the variants
X = Range(Cells(2, 1), Cells(11, 1))
Y = Range(Cells(2, 2), Cells(11, 2))

For i = 1 To UBound(X)
z = (X(i, 1) ^ 2 + Y(i, 1) ^ 2) ^ 0.5
If z <= distance Then
total = total + 1
End If
Next i
TotalShorter = total
End Function

Gary

has me going in the right direction, but is there a way to have the routine read and compute the distances itself rather that have to input it manually?

here is what i have

Sub TotalShorter()
Dim X As Variant, Y As Variant
Dim z As Single
Dim i As Integer, total As Integer

grid = Application.InputBox(Prompt:="what is the grid size?", Type:=1)
diameter = Application.InputBox(Prompt:="what is the diameter of the nozzle spray?", Type:=1)
radius = diameter / 2

total = 0
For i = 0 To grid
For j = 0 To grid
If ((2 - i) ^ 2 + (3 - j) ^ 2) ^ 0.5 <= radius Then
Cells(j + 2, i + 2) = total + 1
Else
Cells(j + 2, i + 2) = total
End If
Next j
Next i
End Sub

the bold/red numbers are for one data point...i am still having some trouble reading in a data range (10 total points)to where i can substitue a Xi and a Yi (data pair) into the routine

range is S2:T11

tried this...



Sub TotalShorter()
Dim X As Variant, Y As Variant
Dim z As Single
Dim i As Integer, total As Integer

grid = Application.InputBox(Prompt:="what is the grid size?", Type:=1)
diameter = Application.InputBox(Prompt:="what is the diameter of the nozzle spray?", Type:=1)
radius = diameter / 2



X = Range(Cells(2, 19), Cells(11, 19))
Y = Range(Cells(2, 20), Cells(11, 20))

total = 0
For i = 0 To grid
For j = 0 To grid
If ((X(i + 2, 19) - i) ^ 2 + (Y(j + 2, 20) - j) ^ 2) ^ 0.5 <= radius Then
Cells(j + 2, i + 2) = total + 1
Else
Cells(j + 2, i + 2) = total
End If
Next j
Next i
End Sub

and i get

run time error "9":
subscript out of range