VBA海量数据摘要

Question

我已经找了好几个星期了，一直在做这个任务，但一直没有找到解决办法。我希望你们能给我一个解决办法。

我的情况如下：

1. 我有2本excel工作簿 (第一: Log.xlsx -总结数据， 第二部分: data.xls -数据源)
2. 在log.xlsx中，我有生产日期、产品id和lot#
3. 在data.xls中，我有生产产量结果(不同id的工作表) FYI，工作表编号约为100个工作表。
4. 我想搜索相同日期的产量结果(data.xls)、产品id和lot#(与 (从Log.xlsx)，然后在Log.xlsx中显示产量结果
5. data.xls工作表名称与产品id不同，但实际产品id显示在 细胞B2

你能告诉我怎样才能做到这一点吗？

真的很感谢你的帮助。

谢谢。

Answer 1

假设代码在log.xlsx中，并且两个工作簿当前都是打开的，这将将列4到7从data.xls复制到log.xlsx。

Option Explicit

Public Sub DataSummary()
Dim wbData As Workbook
Dim wsLog As Worksheet
Dim wsDataSet As Worksheet
Dim i, j, k As Integer
Dim prodDate As Date
Dim prodID As String
Dim lotNumber As Integer

Set wbData = Workbooks("Data.xls")
Set wsLog = ThisWorkbook.ActiveSheet

For i = 2 To wsLog.UsedRange.Rows.Count
    prodDate = wsLog.Cells(i, 1).Value
    prodID = wsLog.Cells(i, 2).Value
    lotNumber = wsLog.Cells(i, 3).Value
    For Each wsDataSet In wbData.Worksheets
        If wsDataSet.Cells(2, 2).Value = prodID Then
            For j = 4 To wsDataSet.UsedRange.Rows.Count
                If wsDataSet.Cells(j, 1).Value = prodDate And wsDataSet.Cells(j, 3).Value = lotNumber Then
                    wsLog.Cells(i, 4).Value = wsDataSet.Cells(j, 4).Value
                    wsLog.Cells(i, 5).Value = wsDataSet.Cells(j, 5).Value
                    wsLog.Cells(i, 6).Value = wsDataSet.Cells(j, 6).Value
                    wsLog.Cells(i, 7).Value = wsDataSet.Cells(j, 7).Value
                    Exit For
                End If
            Next j
            Exit For
        End If
    Next wsDataSet

Next i

End Sub

下面链接到带有代码和示例数据的excel文件。

下面是带有附加检查的代码。当没有匹配的时候，它会放"0“。

Option Explicit

Public Sub DataSummary()
Dim wbData As Workbook
Dim wsLog As Worksheet
Dim wsDataSet As Worksheet
Dim i, j, k As Integer
Dim prodDate As Date
Dim prodID As String
Dim lotNumber As Integer
Dim foundIt As Boolean
Dim Message As String

Message = "0"
Set wbData = Workbooks("Data.xls")
Set wsLog = ThisWorkbook.ActiveSheet

For i = 2 To wsLog.UsedRange.Rows.Count
    foundIt = False
    prodDate = wsLog.Cells(i, 1).Value
    prodID = wsLog.Cells(i, 2).Value
    lotNumber = wsLog.Cells(i, 3).Value
    For Each wsDataSet In wbData.Worksheets
        If wsDataSet.Cells(2, 2).Value = prodID Then
            For j = 4 To wsDataSet.UsedRange.Rows.Count
                If wsDataSet.Cells(j, 1).Value = prodDate And wsDataSet.Cells(j, 3).Value = lotNumber Then
                    wsLog.Cells(i, 4).Value = wsDataSet.Cells(j, 4).Value
                    wsLog.Cells(i, 5).Value = wsDataSet.Cells(j, 5).Value
                    wsLog.Cells(i, 6).Value = wsDataSet.Cells(j, 6).Value
                    wsLog.Cells(i, 7).Value = wsDataSet.Cells(j, 7).Value
                    foundIt = True
                    Exit For
                End If
            Next j
            Exit For
        End If
    Next wsDataSet
    If Not foundIt Then
        wsLog.Cells(i, 4).Value = Message
        wsLog.Cells(i, 5).Value = Message
        wsLog.Cells(i, 6).Value = Message
        wsLog.Cells(i, 7).Value = Message
    End If
Next i

End Sub

请记住，这只是粗略的实际解决方案，您需要添加异常处理，更多的检查，看看数据是否正确，等等。