Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA formating borders (Excel 2000)

    I am having a problem trying to create borders over a range of cells in vba. The code works correctly when I do it on one cell, but doesn't work when placed inside of a loop.
    Dim r As Integer
    Dim c As Integer

    With Worksheets("Table 3")

    random stuff...

    For r = 7 To Cell_Count - 1
    For c = 1 To c = 20
    With .Cells(r, c).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With

    Next c
    Next r

    I am pretty confident "Cell_Count-1" is not the problem because it works successfully with other operations in the event.

  2. #2
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA formating borders (Excel 2000)

    try changing the line "For c = 1 To c = 20" to "For c = 1 To 20" - any better?

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

    Re: VBA formating borders (Excel 2000)

    How is "Cell_Count" dimensioned and typed? And

    For c = 1 To c = 20

    Is not valid syntax.

    Usually the code would be something like (untested air code, and notice the leading dots):

    With myRange
    For r = 7 To (.Rows.Count - 1)
    For r = 7 To (.Columns.Count)
    With .Cells(r,c).Borders
    ... etc

    Afterthought - the code is going to error out (or maybe just do nothing) for any range where the row or column count is < 7.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    New Lounger
    Join Date
    Jun 2005
    Location
    Connecticut, USA
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA formating borders (Excel 2000)

    Changing that made it a lot better. I can't belief I made that mistake. Thanks for the help

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA formating borders (Excel 2000)

    The following works for me, as long as I set Cell_Count greater than 8.

    <code>
    For r = 7 To Cell_Count - 1
    For c = 1 To 20
    With ActiveSheet.Cells(r, c).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With
    Next c
    Next r
    </code>
    Legare Coleman

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: VBA formating borders (Excel 2000)

    Perhaps a loop is not required.

    Would <pre> With Range(Cells(7, 1), Cells(Cell_Count - 1, 20)).Borders
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = xlAutomatic
    End With</pre>

    do the same job ?

    Andrew C

Posting Permissions

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