Hi all,
I would like to compare data in between two workbooks. However, my code doesn't seem to work. It doesn't seem to compare data in between the workbooks. Also, I want all the updated data to be added in the next blank row. However, it seems to add in the position it is instead of in next blank row.
I would like to compare data in between two workbooks. However, my code doesn't seem to work. It doesn't seem to compare data in between the workbooks. Also, I want all the updated data to be added in the next blank row. However, it seems to add in the position it is instead of in next blank row.
Code:
Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim strRangeToC As String
Dim iRow As Long
Dim iCol As Long
Dim wbkA As Workbook
Dim eRow As Long
Dim nlin As Integer
Dim ncol As Integer
Set wbkA = Workbooks.Open(Filename:="C:\Users\mary\Desktop\main.xlsx")
strRangeToCheck = "A:C"
strRangeToC = "C:E"
'Debug.Print Now
varSheetA = wbkA.Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = ThisWorkbook.Worksheets("Sheet1").Range(strRangeToC)
'Debug.Print Now
'Warning: VarSheetA and VarSheetB result in arrays of the ENTIRE respective workbook
' using LBound() to UBound() causes the ENTIRE workbook to be processed,
' regardless of how much is actually used.
' I'd suggest using something like:
' thisworkbook.select
' MaxRow = ActiveCell.SpecialCells(xlLastCell).Row
' MaxCol = ActiveCell.SpecialCells(xlLastCell).Column
' For iRow = 1 to MaxRow
' For iCol = 1 to MaxCol
'--------------------------------------
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
' Cells are identical.
' Do nothing.
Else
eRow = ThisWorkbook.Worksheets("Sheet1").Range("C65536").End(xlUp).Row + 1
wbkA.Sheets("Sheet1").Range("A" & iRow & ":C" & iRow).Copy Destination:=ThisWorkbook.Sheets("Sheet1").Range("C" & eRow & ":E" & eRow)
'eRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row + 1
'ThisWorkbook.Sheets("Sheet1").Range("C" & eRow & ":E" & eRow).Value = wbkA.Sheets("Sheet1").Range("A" & iRow & ":C" & iRow).Value
Exit For
End If
Next
Next
wbkA.Close savechanges:=False
End Sub
