Forum Discussion
Data Sheet Compare Macro
The issue you're encountering seems to be related to how you're attempting to compare rows in your tables. Let's try to address each problem individually:
- Run-time error '438' - Object doesn't support this property or method: This error occurs because you're trying to use the .Range property on a ListObject object. However, the ListObject doesn't have a .Range property. Instead, you should access the individual cells using the .DataBodyRange property of the ListObject. You've already used this correctly in your first loop. So, you should apply the same approach to your second loop.
- Compile Error: Argument not optional: This error occurs because the Join function requires two arguments - the array you want to join and the delimiter. It seems you're missing the delimiter argument in your Join function calls.
Here's how you can maybe fix your second loop and address these issues:
Sub CompareTwoTables()
Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject
Dim rngCell As Range, rowToday As ListRow, rowYesterday As ListRow
Dim foundRow As Boolean
' Reference Tables
Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData")
Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData")
' Loop 1: Highlight changed cells
For Each rngCell In TodayDataTable.DataBodyRange
If rngCell.Value <> YesterdayDataTable.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
rngCell.Interior.Color = vbYellow
rngCell.Font.Color = vbRed
End If
Next rngCell
' Loop 2: Highlight new rows
For Each rowToday In TodayDataTable.ListRows
foundRow = False
For Each rowYesterday In YesterdayDataTable.ListRows
If Join(Application.Transpose(rowToday.Range.Value), "|") = Join(Application.Transpose(rowYesterday.Range.Value), "|") Then
foundRow = True
Exit For
End If
Next rowYesterday
' If the row doesn't exist in the "Yesterday Data" table, it's a new row
If Not foundRow Then
rowToday.Range.Interior.Color = vbYellow
rowToday.Range.Font.Color = vbGreen
End If
Next rowToday
End Sub
In the second loop, I've made the following changes:
- Changed rowToday and rowYesterday from Range objects to ListRow objects, as they represent entire rows in your tables.
- Ensured that the delimiter argument (|) is included in the Join function calls.
Try running this updated code, and it should resolve the errors you've encountered. The text, steps and the code were created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
After doing some more research, I found the error is fixed when performing a redundant double transposition. I do not know if this changes the function of that line but I think that error was due to it not inputting a 1 dim array into the "Source Array" for the Join("Source Array" , Delimiter: "|"). Please let me know if this affects the function of the code since I am now seeing issues with the output format.
Please see the following:
Sub CompareTwoTables()
Dim YesterdayDataTable As ListObject, TodayDataTable As ListObject
Dim rngCell As Range, rowToday As ListRow, rowYesterday As ListRow
Dim foundRow As Boolean
' Reference Tables
Set YesterdayDataTable = Worksheets("YESTERDAY SHEET").ListObjects("YesterdayData")
Set TodayDataTable = Worksheets("TODAY SHEET - MACRO").ListObjects("TodayData")
' Loop 1 -
For Each rngCell In TodayDataTable.DataBodyRange
If rngCell.Value <> YesterdayDataTable.DataBodyRange.Cells(rngCell.Row, rngCell.Column).Value Then
rngCell.Interior.Color = vbYellow
rngCell.Font.Color = vbRed
End If
Next rngCell
' Loop 2 -
For Each rowToday In TodayDataTable.ListRows
foundRow = False
For Each rowYesterday In YesterdayDataTable.ListRows
If Join((Application.Transpose(Application.Transpose(rowToday.Range.Value))), "|") = Join((Application.Transpose(Application.Transpose(rowYesterday.Range.Value))), "|") Then
foundRow = True
Exit For
End If
Next rowYesterday
If Not foundRow Then
rowToday.Range.Font.Color = vbGreen
rowToday.Range.Interior.Color = vbYellow
End If
Next rowToday
End SubThe output of the second loop seems to overwrite the first loop formatting on the "Today Data Table" as it highlights all cells yellow and changes all font to green (See attached - "Table Error (All Values Overwrited by Loop 2)". The following code works for the first loops as intended but still gets overwrited by the second loop (See attached - "Loop 1 - Correct Format"):
' Loop 1 -
For Each rngCell In TodayDataTable.DataBodyRange
If rngCell.Value <> YesterdayDataTable.Range(rngCell.Row, rngCell.Column).Value Then
TodayDataTable.Range(rngCell.Row, rngCell.Column).Interior.Color = vbYellow
TodayDataTable.Range(rngCell.Row, rngCell.Column).EntireRow.Font.Color = vbRed
End If
Next rngCell
End SubI know you said your answers are voluntary and not guaranteed so I appreciate any assistance you can provide.. I think the main function is there I am just wondering if you had any guidance on any additional small kinks to the code.
Thanks again man!
Marshall
found the error is fixed when performing a redundant double transposition.
What did you mean redundant double transposition in specific?
Do you have any further question?
Re:
Is there a way I can write this to reference the tables instead of the separate excel sheets?
Why do you want refer to tables instead of range?
If your source data are imported from other sheet,change to table need additional action.
.png){kind=link}