一、基础操作
1.添加4月1日到4月30日的所有表
Sub text()
Dim i
For i = 1 To 30
Sheets.Add after:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "4月" & i & "日"
Next
End Sub
输出
连续删除
Sub text()
Sheets.Application.DisplayAlerts = False
Dim i
For i = 1 To 30
Sheets(Sheets.Count).Delete
Next
Sheets.Application.DisplayAlerts = False
End Sub
2.添加并修改表名
Sub text()
Sheets.Add after:=Sheet1
Sheet2.Name = "叶问"
End Sub
3.将表复制到最后一个，并且重命名
Sub text()
Sheets("sheet1").Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "lili"
End Sub
4.提取表的名称并汇总
Sub text()
Sheets("汇总").Range("a1") = "姓名"
Dim i
For i = 2 To 4
Sheets("汇总").Range("A" & i) = Sheets(i).Name
Next
End Sub
5.提取工作表的名称及数值
Sub text()
Dim i
For i = 2 To Sheets.Count
Sheets("汇总表").Range("a" & i) = Sheets(i).Name
Sheets("汇总表").Range("b" & i) = Sheets(i).Range("a2")
Next
End Sub
6.移动
Sub text()
Sheets("4月1日").Move before:=Sheets("汇总表")
End Sub
move 与copy 的区别
move只移动位置，原表不保留
copy保留原表
7.利用变量给所有表中的单元格赋值
Sub text()
Dim i, j
For j = 2 To Sheets.Count
Set i = Sheets(j)
i.Range("A1") = 500
Next
End Sub
8.对多个表同时计算
Sub text()
Dim i, s1, s, j
For i = 1 To Sheets.Count
Set s1 = Sheets(i)
s = 0
For j = 2 To 4
s = s + s1.Range("b" & j)
Next
s1.Range("e1") = s
Next
End Sub
9.分模块汇总计算
Option Explicit
Sub 总分()
Dim i, s1, s, j
For i = 1 To Sheets.Count
Set s1 = Sheets(i)
s = 0
For j = 2 To 4
s = s + s1.Range("b" & j)
Next
If s1.Name <> "汇总" Then
s1.Range("e1") = s
End If
Next
End Sub
Sub 汇总()
Dim i, s1
For i = 2 To Sheets.Count
Set s1 = Sheets(i)
Sheets("汇总").Range("A" & i) = s1.Name
Sheets("汇总").Range("b" & i) = s1.Range("e1")
Next
End Sub
Sub 合并()
Call 总分
Call 汇总
End Sub
10.添加控件
二、函数
创建并调用函数
1.
Function 随机数()
随机数 = Int(Rnd * 10) + 1
End Function
Sub 使用函数()
MsgBox 随机数()
End Sub
单元格中用 =随机数() 调用
2.
Function 称呼(x)
If x = "女" Then
称呼 = "女士"
Else
称呼 = "男士"
End If
End Function
Sub text()
Dim i, s
For i = 2 To 7
Set s = Range("b" & i)
Range("c" & i) = 称呼(s)
Next
End Sub
3.截取数据
Sub text()
Dim s
Set s = Range("a2")
城市 = InStr(s, "市")
区县 = InStr(s, "区")
Range("E2") = Left(s, 城市)
Range("f2") = Mid(s, 城市 + 1, 区县)
Range("g2") = Right(s, Len(s) - 区县)
End Sub
更高效更准确的用正则表达式，和其他语言也是通过用的
4.
Sub text()
Dim s, i
For i = 2 To 5
Set s = Range("a" & i)
Range("b" & i) = Split(s, "-")(2) & "年 第" & Split(s, "-")(3) & "周"
Next
End Sub
5.从身份证中提取出生日期
Sub sr()
Dim x, i
i = 2
Do While Range("a" & i) <> ""
x = Range("a" & i)
Range("b" & i) = DateSerial(Mid(x, 7, 4), Mid(x, 11, 2), Mid(x, 13, 2))
i = i + 1
Loop
End Sub
6.for each next
把每个工作表名汇总
Option Explicit
Sub text()
Dim i, j
i = 2
For Each j In Worksheets
If j.Name <> "汇总" Then
Range("a" & i) = j.Name
i = i + 1
End If
Next
End Sub
再逐一删除
Sub 删除()
Excel.Application.DisplayAlerts = False
Dim j
For Each j In Worksheets
If j.Name <> "汇总" Then
j.Delete
End If
Next
Excel.Application.DisplayAlerts = True
End Sub
以下两者相等
For Each j In Worksheets
For i = 1 To Sheets.Count
Set w = Sheets(i)
for each next 在单元格中的应用
Sub a()
Dim i, s1
i = 1
For Each s1 In Range("A1:A10")
s1.Value = i
i = i + 1
Next
End Sub
for each 实战
Sub 汇总()
Dim i, s1, j, k
Set s1 = Sheets("汇总")
For i = 2 To 7
Name = s1.Range("a" & i)
For Each j In Worksheets
If Right(j.Name, 1) = "月" Then
k = 2
Do While j.Range("a" & k) <> ""
If j.Range("a" & k) = Name Then
s1.Range("b" & i) = s1.Range("b" & i) + j.Range("b" & k)
End If
k = k + 1
Loop
End If
Next
Next
End Sub
三、属性
Sub a()
Range("b2") = ThisWorkbook.Name
Range("b3") = ThisWorkbook.Path
Range("b4") = ThisWorkbook.FullName
End Sub
😊以上是我的笔记整理，希望对需要的朋友有所帮助！
