Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Range Objects in an array (Excel 97 VBA)

    Need a little help....I'm trying to group a variable number of objects on a worksheet, but I can't seem to get the syntax right for the array....I can't group all of the objects because I need to have more than one group on the same page....whenever I loop through the variable and try to add another object to the variable "myrange", I get an error saying object not found....does anyone have any advice on how to create a grouped range object that has a variable object count?

    Thanks,

    Tango


    MyQuote = Chr(34)
    If Mycount > 1 Then Myseperator = MyQuote & ", " & MyQuote

    myrange = myrange & Myseperator & "pallet" & Palnum

    ActiveSheet.Shapes.Range(Array(myrange)).Group

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    When you are creating a range object you need to use the 'Set' statement. You may need to post more of your code, since it's not clear what you are attempting, but try setting 'myrange' like this:

    Set myrange = Union(myrange, Range("pallet" & Palnum))

    and don't forget to clear the object after you are done with it:

    Set myrange = Nothing
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    John,

    Thanks for the help....

    Here's more of the code....

    mypallets = Sheets("controls").Range("i4").Value


    'loop through boxes and place them onto layout sheet
    Palnum = 1
    mytop = 30
    'myruns = 3
    myleft = 60
    Mycount = 1

    Sheets("layout").Select
    For Each c In Range("subcats")

    mypallets = c.Offset(0, 7).Value

    Do Until Mycount = mypallets + 1

    ActiveSheet.Shapes("pallet" & Palnum).Select

    MyQuote = Chr(34)
    If Mycount > 1 Then Myseperator = MyQuote & ", " & MyQuote

    myrange = myrange & Myseperator & "pallet" & Palnum

    Palnum = Palnum + 1
    Mycount = Mycount + 1

    Loop

    Worksheets("Layout").Range(Array(myrange)).Group

    Mycount = 1

    Next c

    I'm trying to get it to loop through the number of items in the range "subcat" and then use this to group the number of pallets (1 to x). Therefore, the pallet number can vary, depending upon the subcat. All of the example code I've seen has a static list of products for the arrays, none of them give a variable example.....

    Thanks,

    Tango

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    I'm clueless. These are Shapes that you are trying to Group, correct? Why Group them ,and what do you do with them after you Group them? What is your processing objective? Any chance you could post a censored example (no business references, no real customers/employees. etc.) of the sheets, as I'm spending a bit of time trying to recreate your example?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    It sounds a bit like you're trying to create a control array. Is that the idea?
    Charlotte

  6. #6
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    Here's an example of the code and the objects.....I'm trying to set it up so that the objects can be grouped by the subcategory definition.....the first subcategory only has two items in it, and the next has more, therefore the number can vary from 1 to x.....

    Thanks for the help. This one really has me stumped.....

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    OK, need a little more info.

    You are creating pictures of the layout of product within an endcap. Should there be one endcap for each of the product categories "subcats" on Worksheet Controls in Column B? If so, how does one determine which product (to be placed in an endcap) belongs to which subcat (i.e. how do products map to endcaps)?

    Note; when you create each picture it does not have separate elements, therefore it can't be Grouped; Grouping applies to the layers of elements within a picture. You may be able to create a ShapeRange for each endcap/subcat, but I think it will be easier just to loop through them to place them into separate endcaps by using an outer 'subcat' loop.

    Otherwise, if I'm totally off track, can you explain what it is that you're trying to accomplish in plain old English? <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    (BTW, this is a very interesting use of Excel!)
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Mar 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    John,

    Thanks for the help...yes, the number of items or pallets per group is determined by the number of items in each subcat (the idea was to loop through the number of items and add each item to a group). The reason I wanted to group them by subcat is so that the user would be able to drag and drop the groups into the configuration or layout that they wanted without having to select each pallet seperately.....does this make since?

    Thanks again for the help.....

    Tango

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Grouping Range Objects in an array (Excel 97 VBA)

    Thank you, I still need some clarifications. Are pallets and products the same thing, and are they the same as the products listed in Worksheet "Controls", or is a pallet the same as an endcap? (While I know some of the retail lingo, I don't know it all.) In reponse to my question, "how do products map to endcaps", are you saying that the items in Column C of Worksheet "Controls" are the number of products from Worksheet "Boxes" to go to Worksheet "Layout"? If so, what are the values in Column I of Worksheet "Controls"? I need the relationship between Pallets, Products, Items and Endcaps mapped.

    Then, do you want a picture for each endcap, which then has the products as pictures placed on the picture for each endcap? How many products can be placed on each endcap? And, please don't be offended, but why are you doing all that formatting and layout work when the user can move the pix all about anyway? Why not just list all the products and tell the user to do the display arrangement themselves?
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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