Sub CompareSheetsAndLogChangesColumnB()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
Dim cellA As Range, cellB As Range
Dim pernums As Object
Dim pernum As String, changeInfo As String
Dim data1 As Variant, data2 As Variant
Dim i As Long, changeRow As Long
changeRow = 2
Set ws1 = wb.Sheets("Sheet1")
Set ws2 = wb.Sheets("Sheet2")
Set ws3 = wb.Sheets("Sheet3")
If Not ws3 Is Nothing Then
ws3.Cells.Clear
End If
ws3.Cells(1, 1).Value = "Personal Number"
ws3.Cells(1, 2).Value = "Explanation"
data1 = ws1.UsedRange.Value
data2 = ws2.UsedRange.Value
Set pernums = CreateObject("Scripting.Dictionary")
For i = 2 To UBound(data1, 1)
pernum = data1(i, 2)
If Not pernums.Exists(pernum) Then
pernums.Add pernum, 0
End If
' Call the CompareRows function to compare the entire row
Dim rowChange As String
rowChange = CompareRows(data1, i, data2, ws2, pernum)
If rowChange <> "" Then
ws3.Cells(changeRow, 1).Value = pernum
ws3.Cells(changeRow, 2).Value = rowChange
changeRow = changeRow + 1
End If
Next i
For i = 2 To UBound(data2, 1)
pernum = Data(i, 2)
If Not pernums.Exists(pernum) Then
ws3.Cells(changeRow, 1).Value = pernum
ws3.Cells(changeRow, 2).Value = "Not Found in the first sheet"
changeRow = changeRow + 1
End If
Next i
End Sub
Function CompareRows(data1 As Variant, rowIndex As Long, data2 As Variant, ws2 As Worksheet, pernum As String) As String
' Initialize an empty change string
Dim changeDetails As String
' Find the corresponding row in the second sheet
Dim rowNum2 As Long
rowNum2 = 0
For i = 2 To UBound(data2, 1)
If data2(i, 2) = pernum Then
rowNum2 = i
Exit For
End If
Next i
' Check if the corresponding row was found in the second sheet
If rowNum2 > 0 Then
' Compare each column in the row
For j = LBound(data1, 2) To UBound(data1, 2)
' Check if the column index is within the bounds of data2
If j <= UBound(data2, 2) Then
' Compare the corresponding cell in the second sheet
If data1(rowIndex, j) <> data2(rowNum2, j) Then
Dim header As String
header = FindHeader(ws2, data1(1, j))
If header <> "Header Not Found" Then
' Concatenate the change details
changeDetails = changeDetails & header & ": " & data1(rowIndex, j) & " changed to " & data2(rowNum2, j) & ", "
End If
End If
End If
Next j
' Remove the trailing comma and space if there were changes
If Len(changeDetails) > 0 Then
changeDetails = Left(changeDetails, Len(changeDetails) - 2)
End If
End If
' If the corresponding row was not found in the second sheet, provide an explanation
If rowNum2 = 0 Then
changeDetails = "Personal Number not found in the second sheet"
End If
' If the row is not found in the first sheet, provide a different explanation
If rowIndex > UBound(data1, 1) Then
changeDetails = "Personal Number not found in the first sheet"
End If
' Return the change details for the row
CompareRows = changeDetails
End Function
Function FindHeader(ws As Worksheet, paramValue As Varient) As String
Dim header As Range
On Error Resume Next
Set header = was.Rows(1).Find(paramValue, LookIn:=xlValues, LookAt:=xlWhole)
On Error GoTo 0
If Not header Is Nothing Then
FindHeader = header.Value
Else
FindHeader = "not found"
End If
End Function
字符串
这是我的代码。它完全工作,一切都很好,我只需要让它更有效地工作,使它更快地运行和完成较大的Excel工作表,因为它需要很长的时间,它只是更有效率的手工做。这段代码比较两个工作表,并记录在第三个工作表的变化。这是一个Excel编辑器代码。
4条答案
按热度按时间ego6inou1#
这应该更快。它使用两个字典来Map两个数组中每个id的行位置。还使用一个数组来捕获输出(你可能需要一些更聪明的方式来分配初始大小.),这样你就可以在一次赋值中写入结果。
在我的电脑上比较两张大约90 k行x 8列的表格只花了3秒多。
注意-我省略了列标题检查,因为我无法理解你使用的逻辑。你检查标题是否也在第二个工作表上,但你没有验证标题 * 位置 * 是否与第一个工作表相同.使用
Find()
比使用Application.Match
慢得多,所以至少考虑在现有代码中测试这种变化。字符串
qgzx9mmu2#
它的编写方式似乎可以,如果它做你想要的,但你是重新计算电子表格和重新绘制屏幕上的任何变化是从电子表格,这可能是冰川缓慢!
如果你把现有的代码与禁用屏幕更新和设置计算手动它应该是一个很多快.足够快可能仍然是一个问题,虽然-这肯定是第一件事尝试快速加速.
当你离开你的日常生活时,记得把它重置回正常状态!
示例代码
字符串
ttisahbt3#
你的代码看起来不错。你为范围分配范围的部分非常好,将为你保存很多时间。
然而,有一些规则可以让你的代码更快,在写新代码的时候,你应该记住这些规则。
规则#1.计算
通常,如果单元格或区域中的值发生更改,Excel将重新计算所有单元格、公式和条件格式。这可能会导致工作簿重新计算的频率过高,从而降低性能。您可以使用以下语句阻止Excel重新计算工作簿:
字符串
在代码的末尾,您可以使用以下语句将计算模式设置回自动:
型
但请记住,当计算模式为xlCalculationManual时,Excel不会更新单元格中的值。如果宏依赖于更新的单元格值,则必须使用.Calculate方法(如
Worksheets(1).Calculate.
)强制执行Calculate事件规则2.屏幕更新
正如同事们在评论中已经提到的,你已经可以从这个命令中得到很多东西。
问题是,每次将数据写入工作表时,它都会刷新您看到的屏幕图像。刷新图像对性能的影响相当大。以下命令关闭屏幕更新。
型
在宏结束时,使用以下命令重新打开屏幕更新。
型
规则#3使用语句
您经常会多次操作同一个对象。通过使用With语句一次性对给定对象执行多个操作,可以保存时间并提高性能。
下面的示例中使用的With语句告诉Excel一次应用所有格式更改:
型
With语句示例:
您的代码
型
使用语句进行编码
型
在我的电脑上,使用With Statment的相同代码大约需要15到16秒。
养成将动作分块到With语句中的习惯不仅可以让宏运行得更快,还可以让宏代码更容易阅读。
规则#4忽略事件
如果您为工作簿的Sheet1实现了一个EQUIPMENT_Change事件。任何时候Sheet1上的单元格或区域被更改,EQUIPMENT_Change事件都会运行。因此,如果您有一个标准宏操作Sheet1上的多个单元格,那么每当该工作表上的单元格被更改时,您的宏必须在EQUIPMENT_Change事件运行时暂停。您可以想象这种行为会如何减慢宏的速度。
型
在代码的末尾,可以使用以下语句将EnableEvents模式设置回True:
型
t9aqgxwy4#
日志表变更
的数据
的
主要
字符串
帮助1
型
帮助2
型
帮助3
型
帮助4
型
帮助5
型