Hey allen,
Zie onderstaand een kopie van mijn post op Ozgrid. Helaas nog geen reactie gehad en dacht vraag het hier ook even na.
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.
Ik ben al een tijdje aan het prutsen en kom er maar niet uit, hopelijk kan één van jullie mij helpen. Alvast bedankt.
Ruud
Zie onderstaand een kopie van mijn post op Ozgrid. Helaas nog geen reactie gehad en dacht vraag het hier ook even na.
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.
Code:
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, ""
Else
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)
Else
rCell.Interior.Color = RGB(255, 0, 0)
rCell.Offset(0, -1).Interior.Color = RGB(255, 0, 0)
End If
End If
Next i
Next
MsgBox "Check done", vbOKOnly + vbInformation, ""
End If
Application.ScreenUpdating = True
End SubIk ben al een tijdje aan het prutsen en kom er maar niet uit, hopelijk kan één van jullie mij helpen. Alvast bedankt.
Ruud
