![]() |
|
#1
|
|||
|
|||
|
Hi all,
I've issue in comparing the data in workbooks. For instance, it doesn't update the rows which is being added in the workbook. What I want it to do is to check row by row and update entire row to next blank row in another workbook if the data in the workbook is not same as the data in another workbook. Code:
Sub test()
Dim varSheetA As Variant
Dim varSheetB As Variant
Dim strRangeToCheck As String
Dim strRangeToC As String
Dim iRow As Long
Dim iRow2 As Long
Dim iCol As Long
Dim wbkA As Workbook
Dim eRow As Long
Dim cfind As Range
Dim c As Range
Dim rng As Range
Dim i, j, k As Integer
Dim newarr As String
Dim existarr As String
Dim b As Boolean
Set wbkA = Workbooks.Open(Filename:="C:\Users\pwloo\Desktop\main.xlsx")
strRangeToCheck = "A:C"
strRangeToC = "C:E"
varSheetA = wbkA.Worksheets("Sheet1").Range(strRangeToCheck)
varSheetB = ThisWorkbook.Worksheets("Sheet1").Range(strRangeToC)
For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
For iRow2 = LBound(varSheetB, 1) To UBound(varSheetB, 1)
For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
If ThisWorkbook.Sheets("Sheet1").Range("C").Value = wbkA.Sheets("Sheet1").Range("A") Then
If ThisWorkbook.Sheets("Sheet1").Range("D").Value = wbkA.Sheets("Sheet1").Range("B") Then
If ThisWorkbook.Sheets("Sheet1").Range("E").Value = wbkA.Sheets("Sheet1").Range("C") Then
If varSheetA(iRow, iCol).EntireRow = varSheetB(iRow, iCol).EntireRow Then
' Cells are identical.
' Do nothing
Else
If ThisWorkbook.Sheets("Sheet1").Range("C" & iRow2).Value = wbkA.Sheets("Sheet1").Range("A" & iRow).Value Then
b = False
Else
If ThisWorkbook.Sheets("Sheet1").Range("D" & iRow2).Value = wbkA.Sheets("Sheet1").Range("B" & iRow).Value Then
b = False
Else
If ThisWorkbook.Sheets("Sheet1").Range("E" & iRow2).Value = wbkA.Sheets("Sheet1").Range("C" & iRow).Value Then
b = False
Else
eRow = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 3).End(xlUp).Row + 1
ThisWorkbook.Sheets("Sheet1").Range("C" & eRow & ":E" & eRow).EntireRow = wbkA.Sheets("Sheet1").Range("A" & iRow & ":C" & iRow).EntireRow
Exit For
End If
End If
End If
End If
End If
End If
End If
Next
Next
Next
wbkA.Close savechanges:=False
End Sub
|
|
#2
|
||||
|
||||
|
Cross posted at Excelforum - VBA Express - TheCodeCage
Thread closes
__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
|
| Tags |
| excel vba, vba |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
Copy n Paste
|
seimeinokigen | Excel | 2 | 04-16-2016 09:31 AM |
Copy & Paste Issue
|
anickles | Excel | 1 | 02-11-2015 11:32 AM |
Paste and Copy
|
Quadro | Excel | 4 | 07-15-2014 05:59 AM |
Paste Special: Copy and Paste Formatting Only?
|
tinfanide | Word | 6 | 03-06-2013 12:21 AM |
Word 2007 Copy/Paste Excel issue
|
raven26c | Word | 1 | 11-18-2011 02:49 AM |