时间戳

首页 > 解决方案 > 将工作表数据存储在临时内存中以用于比较 2 excel

vba - 将工作表数据存储在临时内存中以用于比较 2 excel

问题描述

我有一个 VBA 代码来比较来自 2 张工作表的数据，所以我创建了 vba 代码，但它很慢，所以我想以某种方式将工作表数据存储在临时内存中，而是在它之间切换工作表应该从临时内存中使用。

我的代码-

For i = 2 To F1_iRowMax
    Dim RV As Long
    RV = 0

    '   On Error Resume Next
    'RV = Application.WorksheetFunction.Match(F1_Workbook.Sheets(ShName).Cells(i, 16384).Value, F2_Workbook.Sheets(ShName2).Range("XFD1:XFD1048576"), 0)
    RV = Application.WorksheetFunction.Match(F1_Workbook.Sheets(ShName).Cells(i, 16384).Value, KeyRange, 0)

    On Error Resume Next
    If Not IsError(RV) Then

    Else

    End If

    Counter = 0
    Counter = Counter + 1

    pctdone = Counter / F1_iRowMax

    With ufProgress
        .LabelCaption.Caption = "Comparing Part-1 " & i & " of " & F1_iRowMax
        .LabelProgress.Width = pctdone * (.FrameProgress.Width - 10)
    End With

    If RV <> 0 Then
        ''''F1 VS F2
        ' ColumnNumber = iCol_Max
        'ColumnLetter = Split(Cells(1, ColumnNumber).Address, "$")(1)

        For iCol = 1 To iCol_Max
            F1_Data = F1_Workbook.Sheets(ShName).Cells(i, iCol)
            F2_Data = F2_Workbook.Sheets(ShName2).Cells(RV, iCol)

            'If i = 39100 Then Stop  '''for debug

            If F1_Data <> F2_Data Then
                '
                sIdx = sIdx + 1
                '   ThisWorkbook.Sheets("Summary").Cells(sIdx, 1) = F1_Workbook.Sheets(ShName).Cells(iRow, iCol).Address
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 3) = F1_Workbook.Sheets(ShName).Cells(Header, iCol).Value
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 1) = F1_Workbook.Sheets(ShName).Cells(i, 16384).Value
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 2) = F1_Workbook.Sheets(ShName).Cells(i, 1).Value
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 4) = F1_Data
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 5) = F2_Data
                ThisWorkbook.Sheets("Summary").Cells(sIdx, 6) = "Data Mismatch"
                'ThisWorkbook.Sheets("Summary").Cells(sIdx, 3).Select
            End If
        Next iCol
    Else
        sIdx = sIdx + 1
        ThisWorkbook.Sheets("Summary").Cells(sIdx, 1) = F1_Workbook.Sheets(ShName).Cells(i, 16384).Value
        ThisWorkbook.Sheets("Summary").Cells(sIdx, 2) = F1_Workbook.Sheets(ShName).Cells(i, 1).Value
        ThisWorkbook.Sheets("Summary").Cells(sIdx, 4) = "Record Exist"
        ThisWorkbook.Sheets("Summary").Cells(sIdx, 6) = "No Record Found"
        'ThisWorkbook.Sheets("Summary").Cells(sIdx, 3).Select
    End If   
Next i

我想比较两张纸之间的 40k 行。

标签： vbaexcel

解决方案

推荐阅读