因为是班级班长的原因,所以班级的很多杂事都得我去弄。这学期刚开学,辅导员就告诉我要我核对一下班级同学的毕业信息(我们是毕业班),其实也就是姓名,学号,身份证号,生源地,生源地代码之类的。因为生源地一下有3600多个,所以我一个一个用excel查找很麻烦的,其他的信息也要复制,就是这样的杂事。所以我就想能不能用一个脚本来完成这个任务,本来打算是用Python的excel模块,不过下载之后配置好麻烦,就转到VBA,我以前也没有用过VBA,所也是现学现卖。代码如下:
Sub auto()
'
' auto 宏
'
Dim ws_src As Worksheet
Dim ws_dst As Worksheet
Dim ws_addr As Worksheet
Dim i As Integer
Dim j As Integer
Set ws_addr = ThisWorkbook.Worksheets(4)
Set ws_src = ThisWorkbook.Worksheets(1)
'Get the worksheets from the workbooks
Set wbkA = Workbooks.Open(Filename:="C:\Users\mark\Desktop\info.xlsx")
Set ws_dst = wbkA.Worksheets("Sheet1")
For i = 4 To 54
For j = 2 To 3
ws_src.Cells(i - 1, j).Value = ws_dst.Cells(i, j).Value
Next
Next
For i = 4 To 54
For j = 7 To 9
ws_src.Cells(i - 1, j + 3).Value = ws_dst.Cells(i, j).Value
Next
Next
Set dict = CreateObject("Scripting.Dictionary")
For i = 2 To 3638
dict.Add ws_addr.Cells(i, 2).Value, ws_addr.Cells(i, 1).Value
Next
For i = 3 To 53
If dict.Exists(ws_src.Cells(i, 14).Value) Then
ws_src.Cells(i, 13).Value = dict.Item(ws_src.Cells(i, 14).Value)
End If
Next
Set dict = Nothing
End Sub
也没什么复杂的,就是不能再简单的循环,条件逻辑。可能就是字典结构算个亮点吧,VBA有类似C++ Map这样的结构,确实很方便。