Excel VBA编程实例.doc
《Excel VBA编程实例.doc》由会员分享,可在线阅读,更多相关《Excel VBA编程实例.doc(16页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、如有侵权,请联系网站删除,仅供学习与交流Excel VBA编程实例【精品文档】第 16 页Sub direct_Price()定义变量Dim cRows As Integer 总行数Dim cColumns As Integer 总列数Dim HEADERCOLORINDEX As Integer 表头的背景色Dim cTemp As Integer 临时计数Dim sTempString As String 临时字符串变量Dim i As Integer 临时计数Dim j As Integer 临时计数Dim rowIndex As Integer 临时指示处理到哪里Dim colInde
2、x As Integer 临时指示处理到哪里Dim tempRndColor As Integer 临时生成的颜色Dim TABLENAME As String 待处理的表名 Dim colorIndex As String 颜色索引名字表头的背景色HEADERCOLORINDEX = 15colorIndex = 36 颜色从33开始是比较浅的颜色TABLENAME = direct_Price关闭所有弹出的警告消息Application.DisplayAlerts = False设置需要处理的单元表Sheets(TABLENAME).Select 取单元表的总列数与总行数cRows = S
3、heets(TABLENAME).UsedRange.Rows.CountcColumns = Sheets(TABLENAME).UsedRange.Columns.Count 选择所有的单元格 Range(Cells(1, 1), Cells(cRows, cColumns).Select 设置该表中所有单元行高为11.25 Selection.RowHeight = 11.25设置该表中所有单元行高为11.25 Selection.RowHeight = 11.25 设置所有的边框 Selection.Borders(xlDiagonalDown).LineStyle = xlNone
4、Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .colorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .colorIndex = xlAutomatic End With With Selection.Borders(x
5、lEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .colorIndex = xlAutomatic End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .colorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .colorIndex
6、 = xlAutomatic End With 并且拆分所有的单元格 With Selection .MergeCells = False 拆分单格 End With Columns(C:C).Select Selection.Insert Shift:=xlToRight 删除第一列,注意这里必须先拆分单格,再删除第一列,否则一次就会把合并单元格所在列全部删除 Range(Cells(1, 1), Cells(1, 1).Select Selection.EntireColumn.Delete Selection.EntireColumn.Delete 向表头添加一行 Rows(1:1).S
7、elect Selection.InsertColumns(A:A).SelectSelection.ColumnWidth = 9.29Columns(B:B).SelectSelection.ColumnWidth = 6.71Columns(C:C).SelectSelection.ColumnWidth = 15.29Columns(D:D).SelectSelection.ColumnWidth = 29.86Columns(E:E).SelectSelection.ColumnWidth = 12.29Columns(F:F).SelectSelection.ColumnWidth
8、 = 12.29 设定单元格A1:A2 合并A1:A2单元格 Range(A1:A2).Select 将数据写回 With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = True End With 往该单元格中写入Usage_Var ActiveCell.FormulaR1
9、C1 = Price 设置该单元格字体格式 With ActiveCell.Characters(Start:=1, Length:=5).Font .Name = Arial .FontStyle = 加粗 倾斜 .Size = 10 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .colorIndex = 2 End With 单元格设定边框 Selection.Bord
10、ers(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone Selection.Borders(xlEdgeTop).LineStyle = xlNone With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .colorIndex = 56 End With Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
11、 With Selection.Interior .colorIndex = 5 .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With 设定头两行的内部样式 Range(B1:B2).Select Selection.Merge Range(C1:C2).Select Selection.Merge Range(D1:D2).Select Selection.Merge Range(B1:D2).Select 设置头两行行高为11.25 Selection.RowHeight = 14.25 With Selection.Fo
12、nt .Name = Arial .FontStyle = 加粗 .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .colorIndex = xlAutomatic End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText =
13、 True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With With Selection.Interior .colorIndex = HEADERCOLORINDEX .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With Range(B1:B2).Select ActiveCell.FormulaR1C1 = Type With ActiveCell.Cha
14、racters(Start:=1, Length:=4).Font .Name = Arial .FontStyle = 加粗 .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .colorIndex = 5 End With Range(E1:F1).Select With Selection.Font .Name = Arial .FontStyle =
15、加粗 .Size = 8 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .colorIndex = 5 End With With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = Fals
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel VBA编程实例 VBA 编程 实例
限制150内