519678@TOC
VBA笔记
王佩丰VBA学习笔记
(按照课程分类)
- for循环
for i = a to b
next
(先 dim)
Sub gzt()
Rows(“1:1”).Select
Dim i As Integer
For i = 1 To 10
Selection.Copy
ActiveCell.Offset(2, 0).Rows(“1:1”).EntireRow.Select
Selection.Insert Shift:=xlDown
Next
End Sub
Sub gzb()
Rows(“21:21”).Select
Dim i As Integer
For i = 1 To 10
Selection.Delete Shift:=xlUp
ActiveCell.Offset(-2, 0).Rows(“1:1”).EntireRow.Select
Next
End Sub
-
for循环加步长
for i =a to b step cif条件
if a then b
else if c then d
else e
end if退出循环
exit forif条件并列
a and/or b
Sub gzt()
Dim i As Integer
For i = 3 To 2000 Step 2
If Range(“a” & i) = “” Then
Exit For
End If
Rows(“1:1”).Select
Selection.Copy
Range(“A” & i).Select
Selection.Insert Shift:=xlDown
Next
End Sub
Sub gzb()
Dim i As Integer
For i = 3 To 2000
If Range(“a” & i) = “” Then
Exit For
End If
Range(“A” & i).Select
Selection.EntireRow.Delete
Next
End Sub
Sub gsjs()
For i = 2 To 12
If Range(“c” & i) < 3500 Then
Range(“d” & i) = 0
ElseIf Range(“c” & i) >= 3500 And Range(“c” & i) < 5000 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.03
ElseIf Range(“c” & i) >= 5000 And Range(“c” & i) < 8000 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.1 - 105
ElseIf Range(“c” & i) >= 8000 And Range(“c” & i) < 12500 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.2 - 555
ElseIf Range(“c” & i) >= 12500 And Range(“c” & i) < 38500 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.25 - 1005
ElseIf Range(“c” & i) >= 38500 And Range(“c” & i) < 58500 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.3 - 2755
ElseIf Range(“c” & i) >= 58500 And Range(“c” & i) < 83500 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.35 - 5505
ElseIf Range(“c” & i) >= 83500 Then
Range(“d” & i) = (Range(“c” & i) - 3500) * 0.45 - 13505
End If
Next
End Sub
-
操作工作表
Sheet1/Sheets(“名字”)/Sheets(1)
Sheet1.range(“a1”)方法:Select/Add/Delete/Copy
属性:Count/NameSheets.Add after:= Sheets(Sheets.Count), count:=3
解除警告
Excel.Application.DisplayAlerts = FalseSheets(i).range(“a1”).Select 不能跨表操作
Sub cjb()
Dim i, j As Integer
For j = 1 To Sheets.Count
For i = 100 To 2 Step -1
'性别
If Sheets(j).Range(“e” & i) = “男” Then
Sheets(j).Range(“f” & i) = “先生”
Else
Sheets(j).Range(“f” & i) = “女士”
End If
'代号
If Sheets(j).Range(“b” & i) = “理工” Then
Sheets(j).Range(“c” & i) = “LG”
ElseIf Range(“b” & i) = “文科” Then
Sheets(j).Range(“c” & i) = “WK”
Else
Sheets(j).Range(“c” & i) = “CJ”
End If
'删除
If Sheets(j).Range(“d” & i) = “” Then
Sheets(j).Range(“d” & i).EntireRow.Delete
End If
Next
Next
End Sub
-
操作工作簿
方法:Open/Add/Save/CloseWorkbooks.Open Filename:= “”
ActiveWorkbook
for each循环
1.Dim rng As Range
For Each rng In Range(“a1:a10”)
Next
2.Dim sht As Worksheet
For Each sht In Sheets
Next
Sub cjb()
Dim sht As Worksheet
Dim i As Integer
For Each sht In Sheets
For i = 100 To 2 Step -1
'性别
If sht.Range(“e” & i) = “男” Then
sht.Range(“f” & i) = “先生”
Else
sht.Range(“f” & i) = “女士”
End If
'代号
If sht.Range(“b” & i) = “理工” Then
sht.Range(“c” & i) = “LG”
ElseIf Range(“b” & i) = “文科” Then
sht.Range(“c” & i) = “WK”
Else
sht.Range(“c” & i) = “CJ”
End If
'删除
If sht.Range(“d” & i) = “” Then
sht.Range(“d” & i).EntireRow.Delete
End If
Next
sht.Copy
ActiveWorkbook.SaveAs Filename:=“C:\Users\tang\Desktop\1” & sht.Name & “.xlsx”
ActiveWorkbook.Close
Next
End Sub
-
操作单元格
[a1]/Cells(行,列)/Range(“a1”)
单元格的值:Range(“a1”).Value
单元格偏移:Range(“a1”).Offset(行,列)
单元格底部:Range(“a65536”).End(xlUp)
单元格整行:Range(“a1”).EntireRow
单元格区域:Range(“a1”).resize(行,列)
单元格复制:Range(“a1”).Copy Range(“a2”)
合并单元格:Range(“a1:a2”).Merge
清除单元格:Range(“a1”).ClearContents运行宏
Call 宏名筛选
Sheet1.Range(“a1:f1048”).AutoFilter Field:=列数, Criteria1:=””
Selection.AutoFilter (取消筛选状态)
Sub sx()
Dim i As Integer
For i = 2 To Sheets.Count
Sheet1.Selection.AutoFilter
ActiveSheet.Range("$A 1 : 1: 1:F$1048").AutoFilter Field:=4, Criteria1:=Sheets(i).Name
Sheet1.Range(“a2:f” & Range(“a65536”).End(xlUp).Row).Copy Sheets(i).Range(“a2”)
Sheet1.Selection.AutoFilter
Next
End Sub
- MsgBox “”
InputBox “”
Sub hb()
Dim i As Integer
For i = 2 To Sheets.Count
If i = 2 Then
Sheets(i).Range(“a1:f” & Sheets(i).Range(“a65536”).End(xlUp).Row).Copy Sheet1.Range(“a1”)
Else
Sheets(i).Range(“a2:f” & Sheets(i).Range(“a65536”).End(xlUp).Row).Copy Sheet1.Range(“a” & Sheet1.Range(“a65536”).End(xlUp).Row + 1)
End If
Next
Sheet1.Select
End Sub
Sub ss()
Dim i, k As Integer
Dim sht As Worksheet
For i = 1 To 3
k = 0
For Each sht In Sheets
If sht.Name = Sheet1.Range(“a” & i) Then
k = 1
End If
Next
If k = 0 Then
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = Sheet1.Range(“a” & i)
End If
Next
End Sub
Dim i, j, k As Integer
Dim l As Integer
Dim sht, sht1 As Worksheet
Dim irow As Integer '定义行数
l = InputBox(“请问你要按哪列分?”)
Application.DisplayAlerts = False
For Each sht1 In Sheets
If sht1.Name <> “数据” Then
sht1.Delete
End If
Next
Application.DisplayAlerts = True
irow = Sheet1.Range(“a65536”).End(xlUp).Row
'创建
For i = 2 To irow
k = 0
For Each sht In Sheets
If sht.Name = Sheet1.Cells(i, l) Then
k = 1
End If
Next
If k = 0 Then
Sheets.Add after:=Sheets(Sheets.Count)