it has been a while since I have been here and normally I manage to create working codes for documents with help of the information on the forum but at the moment I am stuck and can't seem to find a good code to do what I want.
I have two worksheets with data which are a dumps form our ERP software, I have imported the excel data into two sheets and placed them in tables (one sheet, one table).
The data contains items which still have to be purchased and parts that have already been ordered, some of these parts have an order acknowledgement (OA) and some are still pending for an OA.
To keep a good overview of which items still need to be purchased or still have to be followed up for an OA, I would like to load a new dump from our ERP and compare it with the last dump (let's say a week before).
The new data will be imported on sheet 1 and the historic data can be found on sheet 2.
I would like to check which rows of the new dump are present in the table on sheet 2 and if so, see what items have been changed.
Important is to check if values of 3 different columns in table 1 are present in table 2, only then this row item is the same item as in table 2. Please note that the rows can be anywere, so row number 3 on sheet 1 can correspond with row 10 on sheet 2.
The first image shows the table on sheet 2 with historic dates, the second image shows the table with the new dump on sheet 1.
In yellow the columns are highlighted which will need te be used to check if data from 1 row in table 1 is present in table 2 on sheet 2.
In the second image you see that particular cells are highlighted, these cells contain data which is different (blue) or a complete row that is not present in the historic data (red).
I have tried a lot of codes but cannot figure out the correct approach. See below the code I have ended up with, but maybe a different approach would be better.
- Private Sub CMD_Check_Data_Click()
- Application.ScreenUpdating = False
- Dim Compare_Range_Dump As Range
- Dim iRow As Long
- Dim LastRow As Long
- Dim rCell As Range
- LastRow = Range("A:N").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
- With Worksheets("DUMP Inkopen per productieorder")
- Set Compare_Range_Dump = .Range("E3:E" & LastRow)
- End With
- If IsEmpty(Range("A3")) Then
- MsgBox "No data to check", vbOKOnly + vbInformation, ""
- For Each rCell In Compare_Range_Dump
- Dim i As Integer
- For i = 1 To LastRow
- If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Value Then
- iRow = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & i).Row 'Row where value was found
- If rCell.Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Value & _
- rCell.Offset(0, -1).Value = ThisWorkbook.Sheets("Inkopen per productieorder").Range("E" & iRow).Offset(0, -1).Value Then
- rCell.Interior.Color = RGB(102, 204, 0)
- rCell.Offset(0, -1).Interior.Color = RGB(102, 204, 0)
- rCell.Interior.Color = RGB(255, 0, 0)
- rCell.Offset(0, -1).Interior.Color = RGB(255, 0, 0)
- End If
- End If
- Next i
- MsgBox "Check done", vbOKOnly + vbInformation, ""
- End If
- Application.ScreenUpdating = True
- End Sub
Hopefully you can help me out with the code, I have been trying for a few days. Your help is appreciated!