vba学习code

《vba学习code》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、---sample高级筛选Range("A1:C1120").AdvancedFilterAction:=xlFilterCopy,CriteriaRange:=Range_("E4:F6"),CopyToRange:=Columns("I:K"),Unique:=False---sample命名区域ActiveWorkbook.Worksheets("Sheet1").Names.AddName:="测试命名",RefersToR1C1:=_"=Sheet1!R1C2:R18C7"ActiveWorkbook.Worksheets("

2、Sheet1").Names("测试命名").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'单元格值是任意错误值时，'把字体颜色设置为与单元格底色相同的颜色(即看不出错误值)cell.FormatConditions.AddType:=xlExpression,Formula1:="=or(ISERR(RC),isna(RC))"cell.FormatConditions(1).Font.Color=cell.Interior.Color'单元格值

4、小于0的，全部用红色字体标出cell.FormatConditions.AddType:=xlCellValue,Operator:=xlLess,Formula1:="0"cell.FormatConditions(2).Font.ColorIndex=3EndIfNextcellNextwsEndSub--书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对象[a2]=.Cells.Rows.Count[b1]=Cells.Columns.Count--范围是活动工作表对象[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不然就是整个sheet为对象范围了！！[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