|
Private Sub CommandButton1_Click()
Dim li_rows_1 As Integer
Dim li_rows_2 As Integer
Dim li_row_1 As Integer '计数变量
Dim li_row_2 As Integer '计数变量
Dim aaa1() As String '用变量充当数组上下限要用Redim语句重新声明数组
Dim aaa2() As String
Dim li_columns As Integer
Dim a1, a2, b1, b2 As Integer
li_rows_1 = Sheets("Reference").Range("A" & Sheets("Reference").Rows.Count).End(xlUp).Row
li_rows_2 = Sheets("To Compare").Range("A" & Sheets("To Compare").Rows.Count).End(xlUp).Row
li_columns = Sheets("To Compare").Range("A1").End(xlToRight).Column
ReDim aaa1(li_rows_1, li_columns) As String
ReDim aaa2(li_rows_2, li_columns) As String
w_main.Hide
w_progressbar.Show 0
For a1 = 2 To li_rows_1
For b1 = 1 To li_columns
If aaa Then GoTo CancelProcess
aaa1(a1, b1) = Trim(Sheets("Reference").Cells(a1, b1).Value)
Next
Call progressbar("Loading Page1...", a1 / li_rows_1)
Next
'Unload w_progressbar
w_progressbar.Show 0
For a2 = 2 To li_rows_2
For b2 = 1 To li_columns
If aaa Then GoTo CancelProcess
aaa2(a2, b2) = Trim(Sheets("To Compare").Cells(a2, b2).Value)
Next
Call progressbar("Loading Page2...", a2 / li_rows_2)
Next
'Unload w_progressbar
w_progressbar.Show 0
For a2 = 2 To li_rows_2
For a1 = 2 To li_rows_1 'sheet1's row
If aaa Then GoTo CancelProcess
If aaa2(a2, 1) = aaa1(a1, 1) Then
For b2 = 2 To li_columns
If aaa Then GoTo CancelProcess
If aaa2(a2, b2) <> aaa1(a1, b2) Then
Sheets("To Compare").Cells(a2, b2).Interior.ColorIndex = 6
End If
Next
Exit For
End If
If a1 = li_rows_1 Then
If aaa2(a2, 1) <> aaa1(a1, 1) Then
Sheets("To Compare").Rows(a2).Interior.ColorIndex = 33
End If
End If
Next
Call progressbar("Proceeding...", a2 / li_rows_2)
Next
CancelProcess:
Unload w_progressbar
MsgBox ("Done")
w_main.Show 0
End Sub
|
|