reading data from a range into a cell

[brya6347]

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

 

[GaryDK]

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