vba学习code.docx

---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("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]&")"
Nextcell
EndSub

SubApplySpecialFormattingALL()
ForEachwsInThisWorkbook.Worksheets
ws.UsedRange.FormatConditions.Delete
ForEachcellInws.UsedRange.Cells
IfNotIsEmpty(cell)Then
'单元格值是任意错误值时，
'把字体颜色设置为与单元格底色相同的颜色(即看不出错误值)
cell.FormatConditions.AddType:=xlExpression,Formula1:="=or(ISERR(RC),isna(RC))"
cell.FormatConditions(1).Font.Color=cell.Interior.Color
'单元格值小于0的，全部用红色字体标出
cell.FormatConditions.AddType:=xlCellValue,Operator:=xlLess,Formula1:="0"
cell.FormatConditions(2).Font.ColorIndex=3
EndIf
Nextcell
Nextws
EndSub
--书p111

Subs1hong()
Cells(1,2)=222
[d2].Offset(1,2)=333
[d2].Offset(,1)=444
[d2].Offset(-1)=555
EndSub

Subs1hong()
WithRange("a2:e9")
[a1]=.Cells.Columns.Count—加“.”范围是with里面的range对象
[a2]=.Cells.Rows.Count

[b1]=Cells.Columns.Count--范围是活动工作表对象
[b2]=Cells.Rows.Count

EndWith
EndSub

Subs1hong()

[f1]=Range("b5").End(xlDown).Row
[f2]=Range("b5").End(xlToRight).Column

EndSub

Subs1hong()
WithRange("a1:e20")

lrow=.Cells(.Rows.Count,1).End(xlDown).Row—注意一定要.rows.count不然就是整个sheet为对象范围了！！
[h1]=.Cells(.Rows.Count,1).End(xlDown).Row

Fori=1Tolrow
IfIsEmpty(Cells(i,1))Then
Cells(i,1).Resize(1,4).Interior.ColorIndex=6
EndIf
Nexti

EndWith

EndSub

Subs1hong()
Worksheets("Sheet1").Activate
Range("G21").CurrentRegion.Select
Range("G21").CurrentRegion.SpecialCells(xlCellTypeBlanks).Select

EndSub

Subhong1()
Fori=1To10
Forj=1To10
Ifi=jThen
Cells(i,j)=999
Else
Cells(i,j)=1
EndIf
Nextj
Nexti
EndSub

--sample:乘法口诀
SubmultiplicationTable()
Range("b1:m1").Value=Array(1,2,3,4,5,6,7,8,9,10,11,1