[原创]Excel内容复制对比

Sub SearchAndFill_Click()

Dim Ary1() As Integer
Dim ary2() As Integer

Dim aryTemp1() As String
Dim aryTemp2() As String

Dim Rows__count As Integer

Dim ary3() As Integer
Dim ary4() As Integer
'Dim aryTemp3() As String

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim m As Integer
Dim strTemp1 As String
Dim strTemp2 As String

Rows__count = Sheet3.UsedRange.Rows.Count
k = 0

'/È¡³ö¶ÔÓ¦µÄÁÐÐÅÏ¢
For i = 2 To Rows_count
    strTemp1 = Sheet3.Cells(i, 1).Value
    strTemp2 = Sheet3.Cells(i, 2).Value
    If (strTemp1 = "Match_Data") Then
        '³õʼ»¯ÁÐÆ¥ÅäÐÅÏ¢
        k = 1
        m = 0
    ElseIf (strTemp1 = "Copy_Source") Then
        '³õʼ»¯Êý¾Ý¿½±´ÐÅÏ¢
        k = 2
        m = 0
    Else
        If (strTemp1 <> "") And (k > 0) Then
            If (k = 1) Then
                'Êý×é1£¬Êý×é2·Ö±ð´æ´¢Main, SourceµÄÆ¥ÅäÁÐÐÅÏ¢
                ReDim Preserve Ary1(m) As Integer
                ReDim Preserve ary2(m) As Integer
               
                ReDim Preserve aryTemp1(m) As String
                ReDim Preserve aryTemp2(m) As String
               
                Ary1(m) = GetColumnNum(strTemp1)
                ary2(m) = GetColumnNum(strTemp2)
                m = m + 1
            ElseIf (k = 2) Then
                'Êý×é3£¬Êý×é4·Ö±ð´æ´¢Main, SourceµÄÐÅÏ¢¸´ÖÆÁÐÐÅÏ¢
                ReDim Preserve ary3(m) As Integer
                ReDim Preserve ary4(m) As Integer
               
                ' ReDim Preserve aryTemp3(m) As String
               
                ary3(m) = GetColumnNum(strTemp1)
                ary4(m) = GetColumnNum(strTemp2)
                m = m + 1
            End If
        End If
    End If

Next

'½øÐÐÊý¾Ý±È¶Ô

Dim Rows_countMain As Integer
Dim Rows_countSource As Integer
Dim blnEqual As Boolean

Rows_countMain = Sheet1.UsedRange.Rows.Count
Rows_countSource = Sheet2.UsedRange.Rows.Count

Dim iUpdated As Integer

'Çå¿Õsheet4 ---log¼Ç¼

Dim rng As Range
Dim blnNull As Boolean

Dim iColMax As Integer

iColMax = Sheet1.UsedRange.Columns.Count
iUpdated = Sheet4.UsedRange.Rows.Count

Set rng = Sheet4.Rows("2:" & iUpdated)
rng.Cells.Clear

iUpdated = 0

For i = 2 To Rows_countMain
   
    'È¡Ö÷Êý¾ÝԴÿһÐÐÐèҪƥÅäµÄÐÅÏ¢
        For k = 0 To UBound(Ary1)
            aryTemp1(k) = Sheet1.Cells(i, Ary1(k)).Value
        Next
        'È¡Ô´Êý¾Ý±íËùÓеÄÐÐÐÅÏ¢Óë֮ƥÅä
        For j = 2 To Rows_countSource
            blnEqual = True
            For k = 0 To UBound(ary2)
            aryTemp2(k) = Sheet2.Cells(j, ary2(k)).Value
            If (aryTemp1(k) <> aryTemp2(k)) Then
                blnEqual = False
                Exit For
            End If
            Next
       
            'Èç¹ûÓöµ½ÏàµÈµÄÐÅÏ¢,ÐÅÏ¢Ðи´ÖÆ£¬È»ºóÍ˳öµ±Ç°Ñ­»·
            If (blnEqual = True) Then
               
                blnNull = True
               
                For k = 0 To UBound(ary3)
                    Sheet1.Cells(i, ary3(k)).Value = Sheet2.Cells(j, ary4(k)).Value
                    If (Trim(Sheet2.Cells(j, ary4(k)).Value) <> "") Then
                        blnNull = False
                    End If
                Next
                If (blnNull = False) Then
                    Sheet1.Cells(i, iColMax).Value = "1"
                    iUpdated = iUpdated + 1
                    Sheet4.Cells(iUpdated + 1, 1).Value = i
                    Sheet4.Cells(iUpdated + 1, 2).Value = j
                    Exit For
                End If
            End If
   
        Next

Next

MsgBox "Íê±Ï,×ܹ²" & iUpdated & "Ìõ¼Ç¼±»¸üÐÂ,ÏêϸÐÅÏ¢¿´log¼Ç¼"

End Sub

Function GetColumnNum(ByVal ColumnName As String) As Integer
Dim Result As Integer, First As Integer, Last As Integer

Result = 1
If Trim(ColumnName) <> "" Then
    If Len(ColumnName) = 1 Then
        Result = Asc(UCase(ColumnName)) - 64
    ElseIf Len(ColumnName) = 2 Then
        If UCase(ColumnName) > "IV" Then ColumnName = "IV"
        First = Asc(UCase(Left(ColumnName, 1))) - 64
        Last = Asc(UCase(Right(ColumnName, 1))) - 64
        Result = First_ * 26 + Last
    End If
End If
GetColumnNum = Result

End Function

Function GetColumnName(ByVal ColumnNum As Integer) As String
Dim First As Integer, Last As Integer
Dim Result As String
If ColumnNum > 256 Then ColumnNum = 256
First = Int(ColumnNum / 27)
Last = ColumnNum - (First__ * 26)
If First > 0 Then
Result =_ chr(First + 64)
End If
If Last > 0 Then
Result = Result &_ chr(Last + 64)
End If

GetColumnName = Result
End Function