vba help for database in two workbooks

K

klpw

New member
Joined
Jan 28, 2016
Messages
3
Reaction score
0
Points
0
Hi all,

I'm writing vba code which compares two workbooks. When new data is entered in workbook A, the new data needs to be copied in next blank row in workbook B. However, my code doesn't work. It copies all data from beginning until the end continuously.

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\mandy\Desktop\main.xlsx")
strRangeToCheck = "A:C"
strRangeToC = "C:E"
'Debug.Print Now
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
 

Attachments

  • wanted result.jpg
    wanted result.jpg
    106.4 KB · Views: 26
It's hard for me to see the pictures you posted.
If you could post the workbooks it would be better.
One thing that I see is this.
Code: 
If varSheetA(iRow, iCol).EntireRow = varSheetB(iRow, iCol).EntireRow Then
You should be getting an error as it appears to me that you are copying all cells from 3 columns on one sheet into an array and and all cells from 3 columns on another sheet into an array.

You are comparing one array element to another and entire row is to be used with rows, not array elements.

I did a little test to make sure there was something I wasn't aware of and it threw an error for me.
 
Thank you for your reply. May I know how can I upload my workbook here because I can't find the attachment icon.
 
When you hit the reply button you will get another button labeled go advanced at the bottom of the window, after you click that button you will a paperclip icon will appear on the toolbar, click the paperclick icon and then you will get a window that allows you to browse for the file on your computer and upload it.

If you could make one workbook with a sheet from each of your two files so I can see the data, it would be best.
 
Thank you for your reply. Please see attached for the workbook. For instance, when I run the code for first time, data from 2nd row until 8th row will be pasted because that's the only data and the xlsm file is blank. When second time I run the code, the updated data will be added in next blank row in xlsm file. Please note that the data from first column to third column is unique. There will only be new rows added in database. It will only copy data from database to xlsm file and only copy to next available blank rows instead of the exact position. I was being told that I could use loop through method from Column A to Column C in database then search xlsm file for any match but I am not quite sure how.
 

Attachments

  • fortest.xlsx
    9.2 KB · Views: 14
So some questions I would have would be. I read your post but help me understand a couple of things.

1) Is the goal to make the xlsm sheet an exact copy of the database sheet?
If the answer was yes, then I would say why don't we just clear the xlsm sheet and copy all the database data to the xlsm sheet each time you choose?

2) If the answer to above is no, then I need to know what the difference is between the two sheets. Do we have to make sure the xlsm sheet contains all the database data and the xlsm sheet could contain other data not on the database sheet that you need to keep on the xlsm sheet? It's important for me to know what sheet has what data. Please explain in detail.
 
 
NoS thanks for that.

That has to undoubtedly be the most sites I have ever seen a thread crossposted to.
 
You must log in or register to reply here.
Back
Top