话说懒惰是进步的动力源泉。 现在有这样一个需求:一个主表数据文件(MainData.xls), 内部有5个sheet(sheet1,sheet2,sheet3,sheet4,sheet5),另外有5个数据源文件
(SrcData1.xls, SrcData2.xls, SrcData3.xls, SrcData4.xls, SrcData5.xls),以上5个Sheet和 5个数据源文件是一一对应的, 任务是把5个数据源文件的中的数据分别填充到5个sheet指定的单元格中(匹配信息吻合)。来看代码:
Function FillDataAuto()
Dim TotalRow As Integer
Dim CurRow As Integer
Dim CurColumn As Integer
Dim SheetIndex&, RowIndex1&, RowIndex2&
Dim cls, cls1, cls2, cls3, CurreSheet As String
Dim ColumnC, ColumnJ, ColumnF, ColumnL As Integer
Dim SheetNameArr(5)
Dim SrcFileArr(5)
ColumnB = 2
ColumnC = 3
ColumnE = 5
ColumnF = 6
ColumnH = 8
ColumnJ = 10
ColumnK = 11
ColumnL = 12
SheetNameArr(1) = "Sheet1"
SheetNameArr(2) = "Sheet2"
SheetNameArr(3) = "Sheet3"
SheetNameArr(4) = "Sheet4"
SheetNameArr(5) = "Sheet5"
SrcFileArr(1) = "SrcData1.xls"
SrcFileArr(2) = "SrcData2.xls"
SrcFileArr(3) = "SrcData3.xls"
SrcFileArr(4) = "SrcData4.xls"
SrcFileArr(5) = "SrcData5.xls"
TotalRow = [a65536].End(xlUp).Row
'Scan every Sheet
For SheetIndex = 1 To 5 Step 1
'Scan every row(从第8行开始)
For RowIndex1 = 8 To TotalRow Step 1
CurreSheet = SheetNameArr(SheetIndex)
'获取MainData.xls中的标识码(保存在各个Sheet的第三列(ColumnC))
cls = ActiveWorkbook.Sheets(CurreSheet).Cells(RowIndex1, ColumnC).Value
For RowIndex2 = 1 To TotalRow
'获取SrcData.xls文件中的标识码(保存在sheet为SrcData,第二列(ColumnB)的单元格中)
cls1 = Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnB)
'判断是否为空以及相等与否
If cls1 <> "" And cls = cls1 Then
'读取二重标识码
cls2 = Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnC)
cls3 = ActiveWorkbook.Sheets(CurreSheet).Cells(RowIndex1, ColumnE).Value
If cls2 <> cls3 Then
MsgBox "row(" & RowIndex2 & ")has error!"
Else
'二重标识码匹配的话,将SrcData.xls文件中第ColumnK列的数据填充到MainData.xls中对应的sheet中的ColumnF列中
ActiveWorkbook.Sheets(SheetNameArr(SheetIndex)).Cells(RowIndex1, ColumnF) = _
Application.Workbooks(SrcFileArr(SheetIndex)).Worksheets("SrcData").Cells(RowIndex2, ColumnK)
End If
End If
Next RowIndex2
Next RowIndex1
Next SheetIndex
MsgBox "Data updated!"
End Function
手动输入的话工作量大且容易出错,用代码来实现的话只要半分钟即可完成任务。