vba學(xué)習(xí)code

《vba學(xué)習(xí)code》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。

1、---sample高級(jí)篩選Range("A1:C1120").AdvancedFilterAction:=xlFilterCopy,CriteriaRange:=Range_("E4:F6"),CopyToRange:=Columns("I:K"),Unique:=False---sample命名區(qū)域ActiveWorkbook.Worksheets("Sheet1").Names.AddName:="測(cè)試命名",RefersToR1C1:=_"=Sheet1!R1C2:R18C7"ActiveWorkbook.Worksheets("

2、Sheet1").Names("測(cè)試命名").Comment="none"Submyself_first_test()ForEachcellInRange("a1:a23")[f1].Formula="=min("&[f1]&","&cell.Value&")"[f2].FormulaR1C1="=max(R"&cell.Row&"C"&cell.Column&","&[f2]&")"NextcellEndSubSubApplySpecialFormattingALL()ForEachwsInThisWorkbook.Worksheet

3、sws.UsedRange.FormatConditions.DeleteForEachcellInws.UsedRange.CellsIfNotIsEmpty(cell)Then'單元格值是任意錯(cuò)誤值時(shí)，'把字體顏色設(shè)置為與單元格底色相同的顏色(即看不出錯(cuò)誤值)cell.FormatConditions.AddType:=xlExpression,Formula1:="=or(ISERR(RC),isna(RC))"cell.FormatConditions(1).Font.Color=cell.Interior.Color'單元格值

4、小于0的，全部用紅色字體標(biāo)出cell.FormatConditions.AddType:=xlCellValue,Operator:=xlLess,Formula1:="0"cell.FormatConditions(2).Font.ColorIndex=3EndIfNextcellNextwsEndSub--書(shū)p111Subs1hong()Cells(1,2)=222[d2].Offset(1,2)=333[d2].Offset(,1)=444[d2].Offset(-1)=555EndSubSubs1hong()WithRange(

5、"a2:e9")[a1]=.Cells.Columns.Count—加“.”范圍是with里面的range對(duì)象[a2]=.Cells.Rows.Count[b1]=Cells.Columns.Count--范圍是活動(dòng)工作表對(duì)象[b2]=Cells.Rows.CountEndWithEndSubSubs1hong()[f1]=Range("b5").End(xlDown).Row[f2]=Range("b5").End(xlToRight).ColumnEndSubSubs1hong()WithRange("a1:e20")lrow=.C

6、ells(.Rows.Count,1).End(xlDown).Row—注意一定要.rows.count不然就是整個(gè)sheet為對(duì)象范圍了??！[h1]=.Cells(.Rows.Count,1).End(xlDown).RowFori=1TolrowIfIsEmpty(Cells(i,1))ThenCells(i,1).Resize(1,4).Interior.ColorIndex=6EndIfNextiEndWithEndSubSubs1hong()Worksheets("Sheet1").ActivateRange("G21").C

7、urrentRegion.SelectRange("G21").CurrentRegion.SpecialCells(xlCellTypeBlanks).SelectEndSubSubhong1()Fori=1To10Forj=1To10Ifi=jThenCells(i,j)=999ElseCells(i,j)=1EndIfNextjNextiEndSub--sample:乘法口訣SubmultiplicationTable()Range("b1:m1").Value=Array(1,2,3,4,5,6,7,8,9,10,11,12)R

8、ange("b1:m1").Font.Bold=TrueRange("b1:m1").CopyRange("a2:a13").PasteSpecialTranspose:=TrueRange("b2:m13