Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Grouping (03)

    I have three different group of ranges that I would like to select via VB. I am not sure my approach is sound. The group of ranges would be A1:C1, D1:F1 and finally G1:i1. Note that the ranges will always be adjacent to each other in columns of three.


    I was thinking of something like:

    For i = 1 to 3
    Range(Cells(#,#),Cells(#,#).Select
    Next i

    <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Grouping (03)

    Can you explain what you want to accomplish? In most situations, it is neither necessary nor desirable to select cells in VBA code.

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Grouping (03)

    Hans,

    My main objective is to format the cells of each group. The formatting will be different with every group.

    I figured if I can select the group of cells then I can certainly format them without really selecting them. In reality the i = 1 to 3 will really be 1 to 10 so 10 sets of 3.

    Regards,
    John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Grouping (03)

    You don't need to select cells to format them.
    If the formatting will be different in each group, a loop doesn't seem very useful.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Grouping (03)

    That I understand. I don't quite know how to advance from group one to group two and so forth. I definitely can format the first group. The number of sets changes with each workbook. So in WB01 there may be 10 sets of 3, WB02-4 sets of 3 and etc..

    Still puzzled.
    John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Grouping (03)

    Is there some kind of logical pattern to what you want to do? If not, you might as well write a separate instruction for each group:

    Range("A1:C1").Interior.ColorIndex = 3
    Range("D1:F1").Font.Size = 18
    Range("G1:H1").NumberFormat = "0.00%"
    ...

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Grouping (03)

    I do know the number of groups in each WB as well as the starting column. I guess I can select a cell in the first group, format that group and then ActiveCell.offset(0,3).select and continue with the loop until it finishes the all the groups.

    I'll post an example of the code once I figure it out. Perhaps there will be a better way.

    John

  8. #8
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Grouping (03)

    Hans,

    This is what I came up with:

    Sub TestFormat()
    Dim oCellAddr As Range
    Range("I1").Select
    For i = 1 To 9
    Set oCellAddr = ActiveCell
    Range(Cells(9, ActiveCell.Column - 2), Cells(1075, ActiveCell.Column)).Format the cells
    oCellAddr.Select
    ActiveCell.Offset(0, 3).Select
    Next i

    End Sub


    The code may not be pretty but it gets the job done.

    Regards,
    John

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Grouping (03)

    What is .Format the cells ?

  10. #10
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Grouping (03)

    It doesn't do anything. I should have made a notation referring to that particular line of code which would format the range.

    It was my creative thinking outside the box. I guess it's time to go home.

    Regards,
    John

Posting Permissions

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