1、Subdirect_Price()''定義變量DimcRowsAsInteger'總行數(shù)DimcColumnsAsInteger'總列數(shù)DimHEADERCOLORINDEXAsInteger表頭的背景色'DimcTempAsInteger'臨時計數(shù)DimsTempStringAsString'臨時字符串變量DimiAsInteger'臨時計數(shù)DimjAsInteger'臨時計數(shù)DimrowIndexAsInteger'臨時指示處理到哪里DimcolIndexAsInteger'臨時指示處理到哪里Dimte

2、mpRndColorAsInteger'臨時生成的顏色DimTABLENAMEAsString'待處理的表名DimcolorIndexAsString'顏色索引名字'表頭的背景色HEADERCOLORINDEX=15colorIndex=36'顏色從33開始是比較淺的顏色TABLENAME="direct_Price"'關(guān)閉所有彈出的警告消息=False'設(shè)置需要處理的單元表Sheets(TABLENAME).Select'取單元表的總列數(shù)與總行數(shù)cRows=Sheets(TABLENAME).=Sheets(

3、TABLENAME).'選擇所有的單元格Range(Cells(1,1),Cells(cRows,cColumns).Select'設(shè)置該表中所有單元行高為設(shè)置該表中所有單元行高為'設(shè)置所有的邊框(xlDiagonalDown).LineStyle=xlNone(xlDiagonalUp).LineStyle=xlNoneWith(xlEdgeLeft).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeTop).LineStyle=xlContinuous.Weig

4、ht=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeRight).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlInsideVertical).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWith

5、9;并且拆分所有的單元格WithSelection.MergeCells=False'拆分單格EndWithColumns("C:C").SelectShift:=xlToRight'刪除第一列,注意這里必須先拆分單格，再刪除第一列，否則一次就會把合并單元格所在列全部刪除Range(Cells(1,1),Cells(1,1).Select''向表頭添加一行Rows("1:1").SelectColumns("A:A").SelectColumns("B:B").SelectColum

6、ns("C:C").SelectColumns("D:D").SelectColumns("E:E").SelectColumns("F:F").Select'''''設(shè)定單元格A1:A2'''合并A1:A2單元格Range("A1:A2").Select'將數(shù)據(jù)寫回WithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.Orientatio

7、n=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWith'往該單元格中寫入Usage_Var="Price"'設(shè)置該單元格字體格式With(Start:=1,Length:=5).Font.Name="Arial".FontStyle="加粗傾斜".Size=10.Strikethrough=False.Superscript=False.Subscript=False.Outlin

8、eFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=2EndWith'單元格設(shè)定邊框(xlDiagonalDown).LineStyle=xlNone(xlDiagonalUp).LineStyle=xlNone(xlEdgeTop).LineStyle=xlNoneWith(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlThin.colorIndex=56EndWith(xlInsideHorizontal).LineStyle=xlNoneWith.col

9、orIndex=5.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith''設(shè)定頭兩行的內(nèi)部樣式'''''Range("B1:B2").SelectRange("C1:C2").SelectRange("D1:D2").SelectRange("B1:D2").Select'設(shè)置頭兩行行高為With.Name="Arial".FontStyle="加粗".Siz

10、e=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=xlAutomaticEndWithWithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.Readi

11、ngOrder=xlContextEndWithWith.colorIndex=HEADERCOLORINDEX.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWithRange("B1:B2").Select="Type"With(Start:=1,Length:=4).Font.Name="Arial".FontStyle="加粗".Size=8.Strikethrough=False.Superscript=False.Subscript=False.Outl

12、ineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=5EndWithRange("E1:F1").SelectWith.Name="Arial".FontStyle="加粗".Size=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=5EndW

13、ithWithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=TrueEndWithWith.colorIndex=HEADERCOLORINDEX.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith="Price"Ran

14、ge("E2:F2").Select'設(shè)置頭兩行行高為With.Name="Arial".FontStyle="加粗".Size=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=xlAutomaticEndWithWithSelection.HorizontalAlignment=xlCenter.VerticalAli

15、gnment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWithWith.colorIndex=HEADERCOLORINDEX.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith'加第一二行邊框Range("A1:F2").Select(xlDiagonalDown).LineStyle=xlNone(

16、xlDiagonalUp).LineStyle=xlNoneWith(xlEdgeLeft).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeTop).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeRight).Li

17、neStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlInsideVertical).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlInsideHorizontal).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWith'去掉第三行的:號'sTempString=Right(Cells(3,1),Len(Cells

18、(3,1)-3)'=sTempStringi=2j=1外層循環(huán)判斷是否都合并完成,這里插入了一行，加1Whilei<=cRows'i=i+1Range(Cells(i+1,j),Cells(i+1,j).Select'去掉分類行中的:號If(Len(Cells(i+1,j)>=3)Then''如果是分格的界限If(Left(Cells(i+1,j),3)=":")ThenRange(Cells(i+1,j),Cells(i+1,cColumns).Select'對第三行進行設(shè)定'設(shè)置頭兩行行高為=18Wit

19、h.colorIndex=2.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith'合并前兩格'先將其合并WithSelection.HorizontalAlignment=xlLeft'靠左對齊.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWith'合并'對其設(shè)定字體風格With.Name="Arial".FontSty

20、le="加粗傾斜".Size=9.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=3EndWithWithSelection.HorizontalAlignment=xlLeft.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=

21、False.ReadingOrder=xlContext.MergeCells=TrueEndWithsTempString=Right(Cells(i+1,j),Len(Cells(i+1,j)-3)=sTempStringi=i+1EndIfEndIf'加1后判斷是否到了表尾，沒有繼續(xù)合并處理'If(i<=cRows+1)ThenrowIndex=i'取出Cells(i,j)的內(nèi)容sTempString=Cells(i,j)'循環(huán)判斷下一個單元格是否和上一個單元格相等，不是則表示到此該合并WhilesTempString=Cells(i+1,j)And

22、i<=cRowsi=i+1Wend設(shè)置第一列'''''跳出循環(huán)表示已經(jīng)到此該將rowIndex和i行合并Range(Cells(rowIndex,j),Cells(i,j).Select'將原來內(nèi)容填充進來=sTempString設(shè)合并后的單元格的邊框WithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.R

23、eadingOrder=xlContext.MergeCells=TrueEndWith="加粗"設(shè)置第一列結(jié)束'''''''設(shè)置第二列'''Range(Cells(rowIndex,j+1),Cells(i,j+1).Select'設(shè)置字體With.Name="Arial".FontStyle="加粗".Size=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont

24、=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=5EndWithWithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWith(xlDiagonalDown).LineStyle=xlNone(xlDi

25、agonalUp).LineStyle=xlNoneWith(xlEdgeLeft).LineStyle=xlContinuous.Weight=xlThin.colorIndex=56EndWithWith(xlEdgeTop).LineStyle=xlContinuous.Weight=xlThin.colorIndex=56EndWithWith(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlThin.colorIndex=56EndWithWith(xlEdgeRight).LineStyle=xlContinuous.Weight=xlT

26、hin.colorIndex=56EndWith(xlInsideHorizontal).LineStyle=xlNone設(shè)置第二列結(jié)束''''修改原來單元格的數(shù)據(jù)格式''首先向任一無用的單元格寫入數(shù)據(jù)Range(Cells(cRows+2,cColumns),Cells(cRowscColumns).Select="1"'將其格式拷貝+2,'復(fù)制格式Range(Cells(rowIndex,j+4),Cells(i,cColumns).SelectPaste:=xlPasteAll,Operation:=xl

27、Multiply,_SkipBlanks:=False,Transpose:=False="*#,#"'清除原來內(nèi)容Range(Cells(cRows+2,cColumns),Cells(cRowscColumns).Select+2,設(shè)定數(shù)據(jù)格式完成'''''''統(tǒng)一設(shè)置該區(qū)域的顏色'''''設(shè)置內(nèi)部填充Range(Cells(rowIndex,j),Cells(i,cColumns).SelectcolorIndex=colorIndex+1IfcolorIndex&

28、gt;39ThencolorIndex=33EndIfWith.colorIndex=colorIndex'.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith''''統(tǒng)一設(shè)置該區(qū)域的顏色結(jié)束''''顏色'''''設(shè)置剩余的列'''Range(Cells(rowIndex,j+2),Cells(i,cColumns).Select設(shè)置字體With.Name="Arial".FontSty

29、le="常規(guī)".Size=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=xlAutomaticEndWith'設(shè)置第6列Range(Cells(rowIndex,j+4),Cells(i,j+5).Select'設(shè)置字體With.Name="Arial".FontStyle="常規(guī)".Size=8.Strike

30、through=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=3EndWith設(shè)置全部的邊框'''Range(Cells(rowIndex,j),Cells(i,cColumns).Select'設(shè)置邊框(xlDiagonalDown).LineStyle=xlNone(xlDiagonalUp).LineStyle=xlNoneWith(xlEdgeLeft).LineStyle=x

31、lContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeTop).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeBottom).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlEdgeRight).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticE

32、ndWithWith(xlInsideVertical).LineStyle=xlContinuous.Weight=xlThin.colorIndex=xlAutomaticEndWithWith(xlInsideHorizontal)'.LineStyle=xlContinuous.Weight=xlThin'.colorIndex=xlAutomaticEndWithWend-1,cColumns).SelectCells(rowIndex-1,Range(Cells(rowIndex-1,1),Cells(rowIndex=FalseRange(Cells(rowInd

33、ex-1,cColumns-1),cColumns-1).SelectWithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWithWith.Name="Arial".FontStyle="加粗".Size=8.Strikethrough

34、=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=xlAutomaticEndWithWith.colorIndex=15.Pattern=xlSolid.PatternColorIndex=xlAutomaticEndWith="Average"With(Start:=1,Length:=7).Font.Name="Arial".FontStyle="加粗"

35、.Size=8.Strikethrough=False.Superscript=False.Subscript=False.OutlineFont=False.Shadow=False.Underline=xlUnderlineStyleNone.colorIndex=xlAutomaticEndWith-1,Range(Cells(rowIndex-1,cColumns),Cells(rowIndexcColumns).SelectWithSelection.HorizontalAlignment=xlCenter.VerticalAlignment=xlCenter.WrapText=True.Orientation=0.AddIndent=False.IndentLevel=0.ShrinkToFit=False.ReadingOrder=xlContext.MergeCells=FalseEndWithWith.Name