Array Grid Development

[271553]

Would like to know if there is a way I can have Excel generate a receptor grid (array of x and y) based on specific corner or end points. For example, to generate a 100 meter spacing between zero and 10,000 meters in both x and y directions. Also is there a way to exclude specific locations within the grid array to say form a "doughnut" or area of no data.

 

[Damon Ostrander]

Hi David,

I'm not at all sure what your question is, so maybe others are confused as well. I think some clarification would be helpful.

If you are wanting Excel to draw a special grid, or perhaps position tick marks at certain places on a page or drawing, this is easy to do, but I'm not at all sure if this is what you want. I know it is hard to describe something that is essentially graphical in words, but perhaps you could manually draw a picture using the drawing objects and attach it to your question to help us visualize what you want.

 

[271553]

Damon,

Thanks for the reply. I have manually created a sample grid in Excel as a X-Y Scatter Chart to show all. However, being new to message boards, not sure how to import for display. Where are the drawing object tools you mention within the browser or this website. The cut and paste tool within my browser didn't seem to work. How do you attach files or bring into this reply message box?

Hopefully, my rephrasing the question will preclude me from having to generate a graphic:

I would like Excel to automatically generate data points (receptors), X and Y coordinates in Columns A and B starting from say 0,0 to 10000, 10000 in steps (increment) of 100 meters. I realize that this will create quite a number of rows, but I believe Excel X-Y Scatter charts must have data in two columns.

0 0
0 100
0 200
0 300
.. ..
0 2000
100 0
100 100
100 200
100 300
.. ..
100 2000 . . .
200 100
200 200
200 300
.. ..
200 2000. . .
.. ..
2000 0
2000 100
2000 200
2000 300
.. ..
2000 2000

Any help would be appreciated.

 

[Damon Ostrander]

Hi again David,

I think I understand better now. I did not know that you intended to represent the data on a scatter plot. I believe you just want a macro that automatically generates the data points? If so, here is such a macro:

Sub GridCoordinates()
Dim iRow As Long
Dim Xval As Single
Dim Yval As Single
iRow = 1 'start inserting values in row 1
For Xval = 0 To 10000 Step 100
For Yval = 0 To 10000 Step 100
Cells(iRow, 1) = Xval
Cells(iRow, 2) = Yval
iRow = iRow + 1
Next Yval
Next Xval
Beep 'done
End Sub


A few extra lines of code are needed to eliminate all coordinates that fall within an annular area (I believe you referred to a "donut"). Here's an example:

Sub GridCoordinates()
Dim iRow As Long
Dim Xval As Single
Dim Yval As Single
Dim Rinner As Single 'annulus inner radius
Dim Router As Single 'annulus outer radius
Dim R0x As Single 'annulus center x-coordinate
Dim R0y As Single 'annulus center y-coordinate
Dim Radius As Single

'The following values are just for example purposes
Rinner = 3000
Router = 6000
R0x = 5000
R0y = 5000

iRow = 1 'start inserting values in row 1
For Xval = 0 To 10000 Step 100
For Yval = 0 To 10000 Step 100
Radius = Sqr((Xval - R0x) ^ 2 + (Yval - R0y) ^ 2)
'do not include points within annulus
If Radius < Rinner Or Router < Radius Then
Cells(iRow, 1) = Xval
Cells(iRow, 2) = Yval
iRow = iRow + 1
End If
Next Yval
Next Xval
Beep 'done
End Sub

Regarding the graphical representation, I was really just referring to using the drawings capability (not charts) built in to Excel. But I just found out that the feature of being able to attach files to postings on this BB has not yet been implemented, so that was a bad idea anyway. I hope the above code helps. This code should be pasted into a macro module.