Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Array Grid Development

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    David Weintraub
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    David Weintraub
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •