使用vb的子函数操作Excel电子表格,将多个工作表中的废弃零件中查找一号工厂生产的零件,各个工作表的表明以时间来命名,如201108,而零件编号的3、4位为年份,第5位是A-L的字母,代表1-12月,里面有一个特殊的,S代表9月。不完美的地方是,当读取的零件号所代表的工作表没有时候,程序将报错,停止运行,我没有去改进。
Sub GetData()
Dim neir As String
Dim L As Long
Dim t As Long
Dim nian As String, yue As Integer
Dim yuef As String
Dim mud As String
Dim biaoz As Boolean
Dim i As Integer
Dim hang As Integer
Dim danyg As String
Dim LL As Long
L = Worksheets("Sheet1").[A65536].End(xlUp).Row
For t = 2 To L
neir = Worksheets("Sheet1").Cells(t, 1)
chuanc = Len(neir)
nian = "20" & Mid(neir, 3, 2)
yue = Asc(Mid(neir, 5, 1)) - 65 + 1
If yue < 10 Then
yuef = "0" & yue
End If
mud = nian & yuef
Worksheets(mud).Activate
LL = Worksheets(mud).[A65536].End(xlUp).Row
biaoz = False
For i = 2 To LL
danyg = Worksheets(mud).Cells(i, 1)
If danyg = neir Then
hang = i
Worksheets("Sheet1").Cells(t, 2) = "一号工厂"
Worksheets("Sheet1").Cells(t, 3) = Worksheets(mud).Cells(hang, 3)
Worksheets("Sheet1").Cells(t, 4) = Worksheets(mud).Cells(hang, 2)
biaoz = True
Exit For
End If
Next i
If biaoz = False Then
Worksheets("Sheet1").Cells(t, 2) = "非一号工厂"
End If
Next t
End Sub