A MACRO TO BUILD BORDERS

  • Thread starter Thread starter lar48ry
  • Start date Start date
L

lar48ry

Hi - I am a new member of this news group and also new to VBA for
Excel.
I have some programming background that is rather dated (fortran 77,
Basic, and a specialty language called DAL (which, as far as I know is
no longer in existence)). I have procured a few books and surfed the
Excel help sites and have made some headway however, I find that I need
some help with a macro that I am developing.

The macro is intended to create borders around a selected range of
cells. I started by recording a macro to accomplish this. With a
little work I got this to work when I selected a cell in Column A. Now
I want to expand it to create the same borders by selecting the cells
in Column A that has a numerical value. It can do this either
individually or by activating all the cells in Column A that meets the
requirement and doing them at one time. The other criteria for the
macro is to ignore those rows that have already received their borders
(this part I have not even tackled yet, primarily due to the fact that
I can't get the first part to work yet). I have included a copy of
the macro for your perusal and hopefully comments.

In the macro below I receive a 'Run time error' 424 stating that an
Object is required for the line Lastrow =. I don't understand what
Object it is looking for.

Here's hoping that help is on the way!!! And Thank You in advance.


Sub MULTIBORDERS()
' MULTIBORDERS Macro
' Creates multiple borders based on a number being
' entered into column A
'
'

Dim Lastrow As Long
Dim Row_Index As Long
Dim RW As Integer

'Max number of rows
RW = 395
With ActiveSheet
'Search for the last row with data in Column A
Lastrow = Activate.Cells(Rows.Count, "1").End(xlUp).Row
For Row_Index = RW + 5 To Lastrow Step RW
Next
End With
'ActiveCell.Range("A1:I1").Select

'Create a set of borders for each line of the form that has
'information
Selection.BORDERS(xlDiagonalDown).LineStyle = xlNone
Selection.BORDERS(xlDiagonalUp).LineStyle = xlNone
With Selection.BORDERS(xlEdgeLeft)
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlEdgeRight)
..LineStyle = xlContinuous
..Weight = xlThick
..ColorIndex = xlAutomatic
End With
With Selection.BORDERS(xlInsideVertical)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = xlAutomatic
End With
Exit Sub
End Sub
 
Check out the SpecialCells method of the Range object for information on how
to select cells containing numbers.
 
Hi Lar48ry,
In the macro below I receive a 'Run time error' 424 stating that an
Object is required for the line Lastrow =. I don't understand what
Object it is looking for. [cut]
Lastrow = Activate.Cells(Rows.Count, "1").End(xlUp).Row
Click to expand...

There are two problems with this line:
(1) the initial Activate is not required and is erroneous,
(2) the column reference should either be the numeric 1 without quotes or
the alpha A with the quotes.

In any event the following seems to do what you want:

Sub MULTIBORDERS()
' MULTIBORDERS Macro
' Creates multiple borders based on a number being
' entered into column A
'
Dim rng As Range
Dim cell As Range

On Error GoTo XIT
Set rng = ActiveSheet.Columns("A").Cells
Set rng = rng.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0

For Each cell In rng.Cells
With cell.Resize(1, 9)
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Next cell
Exit Sub

XIT:
MsgBox "No numeric vales found in column A"

End Sub

I have assumed that you wish to apply borders to the cells in columns A:I
for each row that has a numeric constant in column A.

If the number of columns should be diiferent, simply adjust the line:

With cell.Resize(1, 9)

to suit, column 9 being column I.

If the numeric values may be the result of formulae, post back, if you need
to, for a revised version.
 
try this one line (broken with continuation character)

Sub bordernumbers()
Columns(1).SpecialCells(xlConstants, xlNumbers) _
..Borders.LineStyle = xlContinuous
End Sub
 
to do 9 columns and set to thick. The WITH line is ONE line.

Sub bordernumbers()
X = Cells(Rows.Count, 1).End(xlUp).row
With Range(Cells(1, 1), Cells(X, 9)).SpecialCells(xlConstants,
xlNumbers).Borders
..LineStyle = xlContinuous
..Weight = xlThick
End With
End Sub
 
Thanks Don, that also does the job. I am using your previous
suggestion in some print routines as a sort function. Was losing some
hair until that came up, thx a lot for that.

Learning all of the various potential commands is rather intimidating
not to mention frustrating. It is people like you that really help
bring this programming down to a manageable level.

Is there a book that explains just the commands, I have 3 of J.
Whalenbach's books, but am looking for one that is more like a
dictionary than how to.
 
Back
Top