Microsoft Office Forums Copy paste issue for database

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-26-2016, 12:13 AM
klpw klpw is offline Copy paste issue for database Windows 7 64bit Copy paste issue for database Office 2013
Novice
Copy paste issue for database
 
Join Date: Dec 2015
Posts: 5
klpw is on a distinguished road
Exclamation

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

Reply With Quote
  #2  
Old 01-28-2016, 01:30 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Copy paste issue for database Windows 7 64bit Copy paste issue for database Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,387
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Cross posted at Excelforum - VBA Express - TheCodeCage

Thread closes
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
Reply

Tags
excel vba, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy paste issue for database Copy n Paste seimeinokigen Excel 2 04-16-2016 09:31 AM
Copy paste issue for database Copy & Paste Issue anickles Excel 1 02-11-2015 11:32 AM
Copy paste issue for database Paste and Copy Quadro Excel 4 07-15-2014 05:59 AM
Copy paste issue for database Paste Special: Copy and Paste Formatting Only? tinfanide Word 6 03-06-2013 12:21 AM
Copy paste issue for database Word 2007 Copy/Paste Excel issue raven26c Word 1 11-18-2011 02:49 AM


All times are GMT -7. The time now is 09:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft