与 30万 粉丝一起学Excel
VBA里面有一个万能循环语句,超好用,卢子一遇到问题就喜欢用这个,今天也推荐你使用。
如果你这几天有学习,应该很快猜到,就是For Next循环。
1.批量更改工作表名称
昨天已经用循环语句生成12个表,不过实际工作中会对这些工作表名称进行改名字,比如1月-12月。
昨天提到Sheets(2),就代表第2个工作表,如果要知道第2个工作表名称,就用Sheets(2).Name。
现在要将表2的名称改成2月,就可以用下面的代码。
Sheets(2).Name="2月"
里面的数字2可以通过变量i得到,这样完整的代码就处理了。运行后,工作表的名称就变成1月-12月,是不是很简单?
Sub 批量更改工作表名称()
For i = 1 To 12
Sheets(i).Name = i & "月"
Next
End Sub
2.制作目录
当工作表很多的时候,就要制作一个目录,方便后期查找。
写公式的时候单元格是用A1表示,而VBA是用Range ("A1"),略有不同。第几行可以由变量i获得,因此可以写Range ("A" & i)。
这里刚好有10个工作表,就写1 to 10,完整的代码就出来了。
Sub 制作目录()
For i = 1 To 10
Range("A" & i) = Sheets(i).Name
Next
End Sub
假如现在工作表的个数不确定呢?
这里又涉及到一个新单词Sheets.Count,这个就是统计有多少个工作表。
这么多单词记不住怎么办?没关系,你只要记住最前面的就行,后面输入一个.就会提示相关的。这个跟函数的输入差不多,都是边写边提示。
Sub 制作目录()
For i = 1 To Sheets.Count
Range("A" & i) = Sheets(i).Name
Next
End Sub
当然,你这里也可以写得更专业一点,声明变量Dim i As Byte。
Dim 变量名 As 数据类型
专业点的代码就出来了。
Sub 制作目录()
Dim i As Byte
For i = 1 To Sheets.Count
Range("A" & i) = Sheets(i).Name
Next
End Sub
数据类型有非常多,这里先记住2个就行,Byte和Integer都适合这个案例。
3.综合运用,现金日记账汇总
每一家银行日记账格式都一样,余额都放在E列,现在要实现将银行还有对应的最后余额提取到汇总表。
先来看最终效果,点下汇总按钮,1秒钟就搞定,还给你一个提示对话框"报告卢子,提取完毕"。
前面一直有提到目录的代码。
Sub 目录()
Dim i As Integer
For i = 2 To Sheets.Count
Range("a" & i) = Sheets(i).Name
Next
End Sub
接下来,怎么提取每个银行的E列最后一个有金额的值。
先不看VBA,用技巧实现。随便点E列下方空白单元格,按一下Ctrl+↑,就回到E列最后一个有金额的值了。
在最后余额的下方,选一个没有内容的空单元格。比如E3000,E5000都行,因为余额在哪个单元格不确定,为了确保在这个单元格的下方,直接写一个非常大的单元格,比如E60000,也就是Range("E60000")。
方向有下左右上,xlup就是上,也就是Range("E60000").End (xlUp)。
向上以后,是要提取这个单元格的值Value,也就是Range("E60000").End(xlUp).Value。
到此,需要的都有了。
Sub 汇总()
Dim i As Integer
For i = 2 To Sheets.Count
Range("a" & i) = Sheets(i).Name
Range("b" & i) = Sheets(i).Range("E60000").End(xlUp).Value
Next
End Sub
如果要发给其他人使用,经常会加个提示对话框MsgBox "报告卢子,提取完毕",不加也行,没啥影响。
最终代码。
Sub 汇总()
Dim i As Integer
For i = 2 To Sheets.Count
Range("a" & i) = Sheets(i).Name
Range("b" & i) = Sheets(i).Range("E60000").End(xlUp).Value
Next
MsgBox "报告卢子,提取完毕"
End Sub
将原来的内容清除掉,运行代码,验证效果。
学了几天VBA,有没感觉到,其实VBA也不是很难,以前那些很难实现或者很繁琐的事儿交给VBA就对了,全自动生成。
联系客服