VBA-语句汇总

程序错误继续执行OnErrorResumeNext屏幕不更新Application.ScreenUpdating=FalseApplication.ScreenUpdating=True警示为假Application.DisplayAlerts=False关掉文件不保存Windows(oFile.Name).ActivateActiveWorkbook.Closesavechanges:=False定义选中区域的坐标dimx,yx=Selection.Row()'行数y=Selection.Column()'列数单元格所在的行数ActiveCell.Row‘活动单元格所在的行数ActiveCell.Column‘活动单元格所在的列数通过使用行列编号，可用Cells属性来引用单个单元格。该属性返回代表单个单元格的Range对象。下例中，Cells(6,1)返回Sheet1上的单元格A6，然后将Value属性设置为10。SubEnterValue()Worksheets(Sheet1).Cells(6,1).Value=10EndSub因为可用变量替代编号，所以Cells属性非常适合于在单元格区域中循环，如下例所示。SubCycleThrough()DimCounterAsIntegerForCounter=1To20Worksheets(Sheet1).Cells(Counter,3).Value=CounterNextCounterEndSub在命名区域中的单元格上循环下例用ForEach...Next循环语句在命名区域中的每一个单元格上循环。如果该区域中的任一单元格的值超过limit的值，就将该单元格的颜色更改为黄色。SubApplyColor()ConstLimitAsInteger=25ForEachcInRange(MyRange)Ifc.ValueLimitThenc.Interior.ColorIndex=27EndIfNextcEndSub增加一个workbooks,nameCarrierWorkbooks.AddActiveWorkbook.SaveAsFilename:=D:\BOMProduce\carrier.xls,FileFormat:=_xlNormal,Password:=,WriteResPassword:=,ReadOnlyRecommended:=False_,CreateBackup:=False增加一个表单，获取nameSheets.Addx=ActiveSheet.NameSheets(x).Select插入一列Range(E5).SelectSelection.EntireRow.Insert插入一栏Range(F6).SelectSelection.EntireColumn.Insert向右移动一格ActiveCell.Offset(0,-1).Select'当前单元格当前单元格的值ActiveCell.FormulaR1C1=“UseRow”复制表单Windows(spacebom.xls).ActivateCells.SelectSelection.CopyWindows(Bomsetup.xls).ActivateSheets(Sheet2).SelectCells.SelectActiveSheet.PasteRange(A1).Select复制单元格Windows(AkikoResourceBudgetPlan.xls).ActivateRange(BK71).SelectApplication.CutCopyMode=FalseSelection.CopyWindows(Book1.xls).ActivateSheets(Sheet2).SelectActiveSheet.Paste当前单元格整栏选择ActiveCell.EntireColumn.Select、整栏复制与粘贴Columns(C:C).SelectSelection.CopySelection.PasteSpecialPaste:=xlPasteValues,Operation:=xlNone,SkipBlanks_:=False,Transpose:=False两栏进行交换Columns(L:L).SelectSelection.CutColumns(N:N).SelectSelection.InsertShift:=xlToRightDelete:Rows(2:2).SelectSelection.DeleteShift:=xlUpRange(B4).SelectSelection.EntireRow.Delete每列从第k栏开始每5个一列进行排列：Windows(bomsetup.xls).ActivateDimCounterAsIntegerForCounter=2To1000Cells(Counter,11).SelectIfActiveCell.Value=ThenActiveCell.Offset(1,0).SelectElseActiveCell.Offset(1,-5).SelectSelection.EntireRow.InsertActiveCell.Offset(-1,5).SelectRange(Selection,Selection.End(xlToRight)).SelectSelection.CutActiveCell.Offset(1,-5).SelectActiveSheet.PasteEndIfNextCounter字体变色Range(C3).SelectSelection.Font.ColorIndex=3单元格变背景色Selection.Interior.ColorIndex=3字体变粗Range(D4).SelectSelection.Font.Bold=True在B栏中查找是否有0000后Columns(B:B).SelectSetfindxx=Selection.Find(0000)IffindxxIsNothingThen在B栏中查找0000后，向左移动一格Columns(B:B).SelectSelection.Find(What:=0000,After:=ActiveCell,LookIn:=xlFormulas,LookAt_:=xlPart,SearchOrder:=xlByRows,SearchDirection:=xlNext,MatchCase:=_False,MatchByte:=False,SearchFormat:=False).ActivateActiveCell.Offset(0,-1).Select在c栏中找到N/a后用******替代Columns(C:C).SelectSelection.ReplaceWhat:=n/a,Replacement:=******,LookAt:=xlPart,_SearchOrder:=xlByRows,MatchCase:=False,SearchFormat:=False,_ReplaceFormat:=False排序Cells.SelectSelection.SortKey1:=Range(A2),Order1:=xlAscending,Key2:=Range(C2)_,Order2:=xlAscending,Header:=xlYes,OrderCustom:=1,MatchCase:=False_,Orientation:=xlTopToBottom,SortMethod:=xlStroke,DataOption1:=_xlSortNormal,DataOption2:=xlSortNormal自动塞选Cells.SelectSelection.AutoFilterSelection.AutoFilterField:=10‘取消赛选第10栏Selection.AutoFilterField:=10,Criteria1:=#N/A,Operator:=xlAnd‘第10栏选择非#N/A自动运行FormPrivateSubWorkbook_Open()你的窗体.ShowEndSub调整宽度Columns(L:L).EntireColumn.AutoFit代表单元格区域A1:J10Range(Cells(1,1),Cells(10,10))代表单元格区域A1:J10区分颜色并删除SubFilterColor()DimUseRow,ACUseRow=Cells.SpecialCells(xlCellTypeLastCell).RowAC=ActiveCell.ColumnFori=1ToUseRowIfCells(i,AC).Interior.ColorIndexActiveCell.Interior.ColorIndexThenCells(i,AC).EntireRow.deleteEndIfNextEndIfEndSub依次打开选定数据夹中的xls文件Subaa()DimmyDialogAsFileDialog,oFileAsObject,strNameAsString,nAsIntegerDimFSOAsObject,myFolderAsObject,myFilesAsObjectDimySetmyDialog=Application.FileDialog(msoFileDialogFolderPicker)n=1WithmyDialogIf.Show-1ThenExitSubSetFSO=CreateObject(Scripting.FileSystemObject)SetmyFolder=FSO.GetFolder(.InitialFileName)SetmyFiles=myFolder.FilesForEachoFileInmyFilesstrName=UCase(oFile.Name)strName=VBA.Right(strName,3)IfstrName=XLSTheny=oFile.NameWorkbooks.openFilename:=yn=n+1EndIfNextEndWithEndSubSUM变量引用DimnRow1,nRow2AsIntegerDimnColAsIntegernRow1=2nRow2=11nCol=4Range(d12).Formula==sum(d&nRow1&:d&nRow2&)或者ActiveCell.FormulaR1C1==SUM(R[-1]C:R[-&J&]C)XlDirection可为XlDirection常量之一。xlDownxlToRightxlToLeftxlUp示例本示例选定包含单元格B4的区域中B列顶端的单元格。Range(B4).End(xlUp).Select本示例选定包含单元格B4的区域中第4行尾端的单元格。Range(B4).End(xlToRight).Select从单元格B4延伸至第四行最后一个包含数据的单元格。Range(B4,Range(B4).End(xlToRight)).Select引用单元格的值Dimxxxxxx=Workbooks(condition.xls).Worksheets(Sheet1).Range(A1).Value加上格线Subopenfileonebyone()WithSelection.Borders(xlEdgeLeft).LineStyle=xlContinuousEndWithWithSelection.Borders(xlEdgeTop).LineStyle=xlContinuousEndWithWithSelection.Borders(xlEdgeBottom).LineStyle=xlContinuousEndWithWithSelection.Borders(xlEdgeRight).LineStyle=xlContinuousEndW