Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
Check out the SpecialCells method of the Range object for information on how
to select cells containing numbers. On 24 Jan 2005 19:37:08 -0800, "lar48ry" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
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 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. --- Regards, Norman "lar48ry" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
Thanks Norman, works like a charm. Appreciate the time and effort.
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A MACRO TO BUILD BORDERS
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I build this Macro? | Excel Discussion (Misc queries) | |||
Can macro build new macro in another workbook??? Pleeease HELP | Excel Programming | |||
Can macro build new macro in another workbook??? Pleeease HELP | Excel Programming | |||
How to build build a macro that automatically imports | Excel Programming | |||
Build a Macro | Excel Programming |