excelvba常用代码,excelvba经典代码应用大全PDF
VBA-对应excel日常常用操作代码
Sub fuzi()
Range("a1:a22").Copy
Range("b5").Select
ActiveSheet.Paste '在选定区域进行粘贴(含格式)'
Application.CutCopyMode = False
‘不带格式粘贴
Range("a1:a22").Copy
Range("c5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
Sub 运行多少秒()
t = Timer
lrow1 = Range("a1").CurrentRegion.Rows.Count
lrow2 = Range("e1").CurrentRegion.Rows.Count
Range("a2:a" lrow1).Copy
Range("e2:e" lrow2).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "yyyy/m/d"
''--------------------------------------------
lrow3 = Range("b1").CurrentRegion.Rows.Count
lrow4 = Range("f1").CurrentRegion.Rows.Count
Range("b2:b" lrow3).Copy
Range("f2:f" lrow4).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "0.00%"
''---------------------------------------------
lrow5 = Range("c1").CurrentRegion.Rows.Count
lrow6 = Range("g1").CurrentRegion.Rows.Count
Range("c2:c" lrow5).Copy
Range("g2:g" lrow6).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "G/通用格式"
''---------------------------------------------
lrow7 = Range("d1").CurrentRegion.Rows.Count
lrow8 = Range("h1").CurrentRegion.Rows.Count
Range("d2:d" lrow7).Copy
Range("h2:f" lrow8).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "@"
MsgBox Timer - t "秒完成"
End Sub
Sub 向右插入一列()
Worksheets("向右插入一列").Select
Columns("d:d").Insert shift:=xlToRight
End Sub
Sub 取消隐藏()
Worksheets("取消隐藏").Columns("b:h").EntireColumn.AutoFit '仅仅取消b - h列'
'Worksheets().UsedRange. EntireColumn.AutoFit'--整个表都取消
End Sub
Sub 隐藏()
Worksheets("隐藏").Select
Columns("A:H").ColumnWidth = 0
End Sub
Sub 判断统计() '等同于sumif功能'
Worksheets("判断统计").Select
Application.ScreenUpdating = False '关闭屏幕更新，看不到宏的执行过程，但提高宏运行速度
Application.EnableEvents = False '关闭事件，防止触发事情，提高运行速度
a = Range("a1").CurrentRegion.Rows.Count
For i = 2 To a
If Range("p" i) 90 Then
Range("u" i) = "M3+"
ElseIf Range("p" i) = 61 Then
Range("u" i) = "M3"
ElseIf Range("p" i) = 31 Then
Range("u" i) = "M2"
Else: Range("u" i) = "M1"
End If
Next
Range("Y21").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-13],C[-5],""M1"")"
Range("Y22").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-13],C[-5],""M2"")"
Range("Y23").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-13],C[-5],""M3"")"
Range("Y24").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[-13],C[-5],""M3+"")"
Range("Z21").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-6],""M1"")"
Range("Z22").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-6],""M2"")"
Range("Z23").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-6],""M3"")"
Range("Z24").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(C[-6],""M3+"")"
Application.EnableEvents = True
Application.ScreenUpdating = True '结束屏幕更新
End Sub
Sub 匹配()
Worksheets("匹配").Select
Columns("K").Clear
Range("K2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],C[-10]:C[-9],2,0)"
Selection.AutoFill Destination:=Range("K2:K17"), Type:=xlFillDefault
Range("K2:K17").Select
Selection.Copy
Range("K2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M8").Select
Application.CutCopyMode = False
End Sub
Sub 匹配1()
Worksheets("匹配1").Select
Columns("B").Clear
Range("B2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],被匹配!C[-1]:C,2,0)"
Selection.AutoFill Destination:=Range("B2:B17"), Type:=xlFillDefault
Range("B2:B17").Select
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("M8").Select
Application.CutCopyMode = False
End Sub
Sub 排序()
Dim rng As Range
Set rng = Range("A1:A22")
rng.Sort key1:="CONTRACTNO", order1:=xlDescending, Header:=xlYes
End Sub
Sub 筛选()
'筛选
irow = Range("a1").CurrentRegion.Rows.Count - 最大行
ActiveSheet.Range(" 1:A" irow).AutoFilter field:=1, Criteria1:= _
"a15hhhhhhhhhh"
'复制A16
Range("A16").Select
Selection.Copy
'在G16的位置粘贴
Range("G16").Select
'带格式粘贴
ActiveSheet.Paste
'退出粘贴
Application.CutCopyMode = False
'退出筛选
ActiveSheet.Range(" 1:A" irow).AutoFilter field:=1
End Sub
Sub 判断分组()
a = Range("a1").CurrentRegion.Rows.Count
For i = 2 To 43
If Range("A" i) = "M1-1" Then
Range("D" i) = Range("b" i) / Range("c" i) / 0.815
Else: Range("D" i) = Range("b" i) / Range("c" i) / 0.095
End If
Next
End Sub
Sub 单元格相对引用值()
Range("A2").FormulaR1C1 = "=RC[1]RC[2]"
End Sub
Sub 向下填充数据()
Range("b2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]""200"""
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B22"), Type:=xlFillDefault
Range("B2:B22").Select
End Sub
Sub 表格显示隐藏()
Worksheets("表格显示隐藏").Visible = True '打开隐藏表格'
End Sub
Sub 格式调整()
lrow1 = Range("a1").CurrentRegion.Rows.Count
lrow2 = Range("e1").CurrentRegion.Rows.Count
Range("a2:a" lrow1).Copy
Range("e2:e" lrow2).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "yyyy/m/d"
''--------------------------------------------
lrow3 = Range("b1").CurrentRegion.Rows.Count
lrow4 = Range("f1").CurrentRegion.Rows.Count
Range("b2:b" lrow3).Copy
Range("f2:f" lrow4).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "0.00%"
''---------------------------------------------
lrow5 = Range("c1").CurrentRegion.Rows.Count
lrow6 = Range("g1").CurrentRegion.Rows.Count
Range("c2:c" lrow5).Copy
Range("g2:g" lrow6).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "G/通用格式"
''---------------------------------------------
lrow7 = Range("d1").CurrentRegion.Rows.Count
lrow8 = Range("h1").CurrentRegion.Rows.Count
Range("d2:d" lrow7).Copy
Range("h2:h" lrow8).Select
ActiveSheet.Paste
Selection.NumberFormatLocal = "@"
End Sub
Sub 透视表()
Worksheets("透视表").Select
Columns("J:L").Clear
Columns("A:H").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"透视表!R1C1:R20C8", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="透视表!R4C10", TableName:="数据透视表1", DefaultVersion:= _
xlPivotTableVersion15
Sheets("透视表").Select
Cells(4, 10).Select
With ActiveSheet.PivotTables("数据透视表1").PivotFields("组别")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
).PivotFields("可算回款金额"), "求和项:可算回款金额", xlSum
ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
).PivotFields("是否有回退"), "求和项:是否有回退", xlSum
With ActiveSheet.PivotTables("数据透视表1").PivotFields("求和项:是否有回退")
.Caption = "计数项:是否有回退"
.Function = xlCount
End With
ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
Excel的VBA代码解释？
Sub ek_sky()
? Dim arr1, i, j$
? '得到F1:Fx的区域，其中x取决于A列最后一个单元格的行，例如：A1到A10都有内容，A11开始就没内容了，那么arr1得到的区域就是F1:F10
? arr1 = Range("f1:f" Cells(Rows.Count, 1).End(3).Row)
? 'i从1到arr1区域中单元格的数量
? For i = 1 To UBound(arr1)
? ? ? '如果arr1中第(i,1)个元素的内容包含以通配符表示的"*小节*"，那就执行IF内的语句
? ? ? If arr1(i, 1) Like "*小节*" Then
? ? ? ? ? j = j ",A" i
? ? ? End If
? Next i
? 'j变量中保存的是F列中包含有"*小节*"的对应的A列单元格，如",A1,A2"，由于前面有个逗号，所以需要使用Mid(j,2)，以此来从第2个符号开始往后取，以去掉最前面的逗号
? '下面整句的使用就是将包含"*小节*"内容的行全都选中
? Range(Mid(j, 2)).EntireRow.Select
End Sub
《ExcelVBA常用代码实战大全》pdf下载在线阅读，求百度网盘云资源
《Excel VBA常用代码实战大全》（袁竹平）电子书网盘下载免费在线阅读
资源链接：
链接：
提取码：h8h0
书名：Excel VBA常用代码实战大全
作者：袁竹平
豆瓣评分：7.6
出版年份：2010-7
页数：374
内容简介：《Excel VBA常用代码实战大全》重点强调实践性，旨在帮助Excel初学者和有一定ExcelVBA编程基础、希望进一步提高的读者掌握VBA编程设计的技巧。全书精选150个典型范例，近245个技巧，以实用为主，知识点针对性强，并逐层对代码辅以深入浅出地剖析，让读者通过范例的学习，快速将ExcelVBA编程应用到实际生活和工作中。
《Excel VBA常用代码实战大全》主要介绍VBA常用对象的应用技巧、控件及用户窗体的应用技巧、对话框的应用技巧、函数的应用技巧，使用VBA操作文件等技巧，最后介绍了制作员工花名册、收据打印系统、员工考勤系统三个综合实例。
《Excel VBA常用代码实战大全》内容丰富、图文并茂，适合初步掌握VBA的基本知识，但需要进一步提高编程能力的初学者，以及希望通过VBA提高工作效率的各类从业人员。也适合各大、中专院校相关专业师生和企业、相关单位的培训班学员阅读。
Excel之vba代码
hi,本来想把改动的地方标出来，后来发现改动还蛮多的，就不一一标出了，现在应该可以运行了。
Sub Test()
Dim i As Integer, iRow As Integer
Dim strPath As String
Dim TheSheet As Worksheet
iRow = 1
Set TheSheet = ActiveWorkbook.Worksheets("sheet1")
strPath = ""E:\可丢\hua"
With Application.FileSearch
.LookIn = strPath
.SearchSubFolders = True
.Filename = "*.*"
If .Execute 0 Then
For i = 1 To .FoundFiles.Count
'Range("A" i) = .FoundFiles(i)
Workbooks.Open (.FoundFiles(i))
For j = 1 To ActiveWorkbook.Worksheets.Count
'ActiveWorkbook.Worksheets(i).Cells(1, 1).Value = "a"
ActiveWorkbook.Worksheets(j).UsedRange.Copy
TheSheet.Activate
While TheSheet.Range("a" iRow).Value ""
iRow = iRow + 1
Wend
TheSheet.Range("A" iRow).Select
ActiveSheet.Paste
ActiveWorkbook.Save
Next j
Workbooks(Workbooks.Count).Close
Next i
End If
End With
End Sub
Excel 常用函数的VBA代码怎么查看
Alt+F11 打开 VBE 编辑器，然后 F2 打开对象浏览器，
常见的 Excel 函数在 WorksheetFunction 这个类模块下：
如果要查看更具体的帮助，可以选中某个函数，然后 F1 打开帮助文档。
