#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
__________________
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 |
Thread Tools | |
Display Modes | |
|
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 |