Help on macro to compare data

[vbanovice123]

Hi,

I have a macro that compares data between two worksheets. The macro compares the cells and raises a flag if the numbe values differ in 8th decimal. Example if the number are 41.23456789 and 41.23456787 , differences are reported.

I have the below which checks for cell values. I want to ignore decimal places. So for the above example, the macro should check for values 41 and 41 and report no differences.

If Sheet1.Cells(RowIndex, ColIndex).Formula <> Sheet2.Cells(RowIndex, ColIndex).Formula Then

Can I use format or round prior to checking for differences? How can I modify the code?

Sub CompareDiff()

On Error GoTo ErrHandle
Call ClearMarkers

' Determine range of used cells, using the highest row and column counts
' found in either of the two sheets.
Dim HighRow As Long
HighRow = Sheet1.UsedRange.Rows.Count
If Sheet2.UsedRange.Rows.Count > HighRow Then
HighRow = Sheet2.UsedRange.Rows.Count
End If
Dim HighCol As Long
HighCol = Sheet1.UsedRange.Columns.Count
If Sheet2.UsedRange.Columns.Count > HighCol Then
HighCol = Sheet2.UsedRange.Columns.Count
End If

' Walk through the cells of both sheets, comparing and changing colors
' if differences are found.
Dim RowIndex As Long
Dim ColIndex As Long

Dim RowFirst As Long
Dim ColFirst As Long

For RowIndex = 1 To HighRow

For ColIndex = 1 To HighCol

' Compare formulas, not "text" or other formatting-affected attributes.
If Sheet1.Cells(RowIndex, ColIndex).Formula <> Sheet2.Cells(RowIndex, ColIndex).Formula Then

' Determine how to indicate the difference on Sheet1.
If Sheet1.Cells(RowIndex, ColIndex).Text = "" Then
' If the cell contains no text, highlight the empty cell.
Sheet1.Select
Sheet1.Cells(RowIndex, ColIndex).Select
Selection.Interior.ColorIndex = 38
Else
' If the cell contains text, color the text.
Sheet1.Cells(RowIndex, ColIndex).Font.Color = &HFF
End If

' Determine how to indicate the difference on Sheet2.
If Sheet2.Cells(RowIndex, ColIndex).Text = "" Then
' If the cell contains no text, highlight the empty cell.
Sheet2.Select
Sheet2.Cells(RowIndex, ColIndex).Select
Selection.Interior.ColorIndex = 38
Else
' If the cell contains text, color the text.
Sheet2.Cells(RowIndex, ColIndex).Font.Color = &HFF
End If

' If this is the first row containing a difference, remember the cell
' in which the change occurred so we can return to it later.
If RowFirst = 0 Then
RowFirst = RowIndex
ColFirst = ColIndex
End If

End If
Next
Next

' Either report no differences or focus on the first difference found.
If RowFirst = 0 Then
MsgBox "No differences!"
Else
If ThisWorkbook.ActiveSheet.Index = 1 Then
Sheet1.Cells(RowFirst, ColFirst).Activate
End If
If ThisWorkbook.ActiveSheet.Index = 2 Then
Sheet2.Cells(RowFirst, ColFirst).Activate
End If
End If

Exit Sub

ErrHandle:
MsgBox Err.Description
End Sub


Thanks

vbanovice123

 

[VoG]

Try like this

If CLng(Sheet1.Cells(RowIndex, ColIndex).Value) <> CLng(Sheet2.Cells(RowIndex, ColIndex).Value) Then

 

[vbanovice123]

I tried it and it works for some examples. But when I include the code in the macro and run it I get a message:

"Type mismatch".

I should get a message "No Differences".

Is this a syntax error?

 

[VoG]

If the values are text or error values then you would get an error message. Are you only interested in numeric values?

 

[vbanovice123]

I want to compare text values as well but incase of numeric , I want to ignore decimals.

 

[vbanovice123]

I was able to compare data without the Type Mismatch msgbox until comparison of 5 columns in both sheeets. Column 6 onwards I get the msgbox "Type Mixmatch".

I took out all columns beyond column 5 as well I took out the column that contained text data and the macro gives me No Differences.

The data itself has "%" and decimals in the numbers and there are dates as column headers for each column in the format yyyymm.

What can I add in order to prevent the "Type Mismatch" from showing up.

Thanks for your help!

vbanovice123

 

[vbanovice123]

From the msgbox "Tye Mismatch" , it appears that there might be a data type in the columns that is not consistent in the two worksheets. But it makes no sense to me as both worksheets have a general format . I tried changinf the format to Number and still get the same message "Type Mismatch".

What else can I try? Any suggestions?

Thanks

 

[vbanovice123]

I am having the Type mismatch for just the last four rows now.

I formatted the data to be all numbers with a decimal of 20 places. The data is mixed with text. percent and number types.

what is different witht he 4 rows below which is throwing the msgbox "Type MisMatch" ? Any help is appreciated.



NEW DATA


<table border="0" cellpadding="0" cellspacing="0" width="3453"><col style="width: 233pt;" width="311"> <col style="width: 281pt;" width="374"> <col style="width: 277pt;" width="369"> <col style="width: 248pt;" width="331"> <col style="width: 228pt;" width="304"> <col style="width: 271pt;" width="361"> <col style="width: 230pt;" width="306"> <col style="width: 215pt;" width="286"> <col style="width: 273pt;" width="364"> <col style="width: 222pt;" width="296"> <col style="width: 113pt;" width="151"> <tbody><tr style="height: 21pt;" height="28"> <td class="xl65" style="height: 21pt; width: 233pt;" width="311" height="28"> <table border="0" cellpadding="0" cellspacing="0" width="3604"><col style="width: 233pt;" width="311"> <col style="width: 281pt;" width="374"> <col style="width: 277pt;" width="369"> <col style="width: 248pt;" width="331"> <col style="width: 228pt;" width="304"> <col style="width: 271pt;" width="361"> <col style="width: 230pt;" width="306"> <col style="width: 215pt;" width="286"> <col style="width: 273pt;" width="364"> <col style="width: 222pt;" width="296"> <col style="width: 113pt;" width="151" span="2"> <tbody><tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 233pt;" width="311" height="28">0.00066563124029287800</td> <td class="xl67" style="width: 281pt;" width="374">0.00052048995454387700</td> <td class="xl67" style="width: 277pt;" width="369">0.00070412617941135000</td> <td class="xl67" style="width: 248pt;" width="331">0.00073027553857821000</td> <td class="xl67" style="width: 228pt;" width="304">0.00061469111771334900</td> <td class="xl67" style="width: 271pt;" width="361">0.00041407867494824000</td> <td class="xl67" style="width: 230pt;" width="306">0.00041637751561415700</td> <td class="xl67" style="width: 215pt;" width="286">0.00022921760391198000</td> <td class="xl67" style="width: 273pt;" width="364">0.00035480686165617700</td> <td class="xl67" style="width: 222pt;" width="296">0.00031265144054151200</td> <td class="xl67" style="width: 113pt;" width="151"> </td> <td class="xl67" style="width: 113pt;" width="151"> </td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl66" style="height: 21pt; width: 233pt;" width="311" height="28">0.00096146734708971200</td> <td class="xl66" style="width: 281pt;" width="374">0.00097158124848190400</td> <td class="xl66" style="width: 277pt;" width="369">0.00105618926911703000</td> <td class="xl66" style="width: 248pt;" width="331">0.00112350082858186000</td> <td class="xl66" style="width: 228pt;" width="304">0.00094764880647474600</td> <td class="xl66" style="width: 271pt;" width="361">0.00063201481965784000</td> <td class="xl66" style="width: 230pt;" width="306">0.00069396252602359500</td> <td class="xl66" style="width: 215pt;" width="286">0.00036292787286063600</td> <td class="xl66" style="width: 273pt;" width="364">0.00055534987041836400</td> <td class="xl66" style="width: 222pt;" width="296">
</td> <td class="xl66" style="width: 113pt;" width="151">
</td> <td class="xl66" style="width: 113pt;" width="151">
</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 233pt;" width="311" height="28">0.00210783226092745000</td> <td class="xl67" style="width: 281pt;" width="374">0.00242895312120476000</td> <td class="xl67" style="width: 277pt;" width="369">0.00235882270102802000</td> <td class="xl67" style="width: 248pt;" width="331">0.00213465157430554000</td> <td class="xl67" style="width: 228pt;" width="304">0.00210019465218728000</td> <td class="xl67" style="width: 271pt;" width="361">0.00154734662743816000</td> <td class="xl67" style="width: 230pt;" width="306"> </td> <td class="xl67" style="width: 215pt;" width="286"> </td> <td class="xl67" style="width: 273pt;" width="364"> </td> <td class="xl67" style="width: 222pt;" width="296"> </td> <td class="xl67" style="width: 113pt;" width="151"> </td> <td class="xl67" style="width: 113pt;" width="151"> </td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl68" style="height: 21pt; width: 233pt;" width="311" height="28">0.00088750832039050400</td> <td class="xl68" style="width: 281pt;" width="374">0.00104097990908775000</td> <td class="xl68" style="width: 277pt;" width="369">0.00098577665117589100</td> <td class="xl68" style="width: 248pt;" width="331">0.00117967587001095000</td> <td class="xl68" style="width: 228pt;" width="304">0.00087081241676057800</td> <td class="xl68" style="width: 271pt;" width="361">0.00074098289201264000</td> <td class="xl68" style="width: 230pt;" width="306">
</td> <td class="xl68" style="width: 215pt;" width="286">
</td> <td class="xl68" style="width: 273pt;" width="364">
</td> <td class="xl68" style="width: 222pt;" width="296">
</td> <td class="xl68" style="width: 113pt;" width="151">
</td> <td class="xl68" style="width: 113pt;" width="151">
</td> </tr> </tbody></table></td> <td class="xl65" style="width: 281pt;" width="374">
</td> <td class="xl65" style="width: 277pt;" width="369">
</td> <td class="xl65" style="width: 248pt;" width="331">
</td> <td class="xl65" style="width: 228pt;" width="304">
</td> <td class="xl65" style="width: 271pt;" width="361">
</td> <td class="xl65" style="width: 230pt;" width="306">
</td> <td class="xl65" style="width: 215pt;" width="286">
</td> <td class="xl65" style="width: 273pt;" width="364">
</td> <td class="xl65" style="width: 222pt;" width="296">
</td> <td class="xl65" style="width: 113pt;" width="151">
</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl64" style="height: 21pt; width: 233pt;" width="311" height="28">
</td> <td class="xl64" style="width: 281pt;" width="374">
</td> <td class="xl64" style="width: 277pt;" width="369">
</td> <td class="xl64" style="width: 248pt;" width="331">
</td> <td class="xl64" style="width: 228pt;" width="304">
</td> <td class="xl64" style="width: 271pt;" width="361">
</td> <td class="xl64" style="width: 230pt;" width="306">
</td> <td class="xl64" style="width: 215pt;" width="286">
</td> <td class="xl64" style="width: 273pt;" width="364">
</td> <td class="xl64" style="width: 222pt;" width="296">
</td> <td class="xl64" style="width: 113pt;" width="151">
</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl65" style="height: 21pt; width: 233pt;" width="311" height="28">
</td> <td class="xl65" style="width: 281pt;" width="374">
</td> <td class="xl65" style="width: 277pt;" width="369">
</td> <td class="xl65" style="width: 248pt;" width="331">
</td> <td class="xl65" style="width: 228pt;" width="304">
</td> <td class="xl65" style="width: 271pt;" width="361">
</td> <td class="xl65" style="width: 230pt;" width="306">
</td> <td class="xl65" style="width: 215pt;" width="286">
</td> <td class="xl65" style="width: 273pt;" width="364">
</td> <td class="xl65" style="width: 222pt;" width="296">
</td> <td class="xl65" style="width: 113pt;" width="151">
</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl66" style="height: 21pt; width: 233pt;" width="311" height="28">
</td> <td class="xl66" style="width: 281pt;" width="374">
</td> <td class="xl66" style="width: 277pt;" width="369">
</td> <td class="xl66" style="width: 248pt;" width="331">
</td> <td class="xl66" style="width: 228pt;" width="304">
</td> <td class="xl66" style="width: 271pt;" width="361">
</td> <td class="xl66" style="width: 230pt;" width="306">
</td> <td class="xl66" style="width: 215pt;" width="286">
</td> <td class="xl66" style="width: 273pt;" width="364">
</td> <td class="xl66" style="width: 222pt;" width="296">
</td> <td class="xl66" style="width: 113pt;" width="151">
</td> </tr> </tbody></table>

OLD DATA


<table border="0" cellpadding="0" cellspacing="0" width="3125"><col style="width: 227pt;" width="302"> <col style="width: 223pt;" width="297"> <col style="width: 224pt;" width="298"> <col style="width: 218pt;" width="290"> <col style="width: 210pt;" width="280"> <col style="width: 227pt;" width="302"> <col style="width: 245pt;" width="327"> <col style="width: 233pt;" width="310"> <col style="width: 231pt;" width="308"> <col style="width: 103pt;" width="137" span="3"> <tbody><tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 227pt;" width="302" height="28">0.00066563124029287800</td> <td class="xl67" style="width: 223pt;" width="297">0.00052048995454387700</td> <td class="xl67" style="width: 224pt;" width="298">0.00070412617941135000</td> <td class="xl67" style="width: 218pt;" width="290">0.00073027553857821000</td> <td class="xl67" style="width: 210pt;" width="280">0.00061469111771334900</td> <td class="xl67" style="width: 227pt;" width="302">0.00041407867494824000</td> <td class="xl67" style="width: 245pt;" width="327">0.00041637751561415700</td> <td class="xl67" style="width: 233pt;" width="310">0.00022921760391198000</td> <td class="xl67" style="width: 231pt;" width="308">0.00035480686165617700</td> <td class="xl67" style="width: 103pt;" width="137"> </td> <td class="xl67" style="width: 103pt;" width="137"> </td> <td class="xl67" style="width: 103pt;" width="137"> </td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl66" style="height: 21pt; width: 227pt;" width="302" height="28">0.00096146734708971200</td> <td class="xl66" style="width: 223pt;" width="297">0.00097158124848190400</td> <td class="xl66" style="width: 224pt;" width="298">0.00105618926911703000</td> <td class="xl66" style="width: 218pt;" width="290">0.00112350082858186000</td> <td class="xl66" style="width: 210pt;" width="280">0.00094764880647474600</td> <td class="xl66" style="width: 227pt;" width="302">0.00063201481965784000</td> <td class="xl66" style="width: 245pt;" width="327">0.00069396252602359500</td> <td class="xl66" style="width: 233pt;" width="310">0.00036292787286063600</td> <td class="xl66" style="width: 231pt;" width="308">
</td> <td class="xl66" style="width: 103pt;" width="137">
</td> <td class="xl66" style="width: 103pt;" width="137">
</td> <td class="xl66" style="width: 103pt;" width="137">
</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 227pt;" width="302" height="28">0.00210783226092745000</td> <td class="xl67" style="width: 223pt;" width="297">0.00242895312120476000</td> <td class="xl67" style="width: 224pt;" width="298">0.00235882270102802000</td> <td class="xl67" style="width: 218pt;" width="290">0.00213465157430554000</td> <td class="xl67" style="width: 210pt;" width="280">0.00210019465218728000</td> <td class="xl67" style="width: 227pt;" width="302"> </td> <td class="xl67" style="width: 245pt;" width="327"> </td> <td class="xl67" style="width: 233pt;" width="310"> </td> <td class="xl67" style="width: 231pt;" width="308"> </td> <td class="xl67" style="width: 103pt;" width="137"> </td> <td class="xl67" style="width: 103pt;" width="137"> </td> <td class="xl67" style="width: 103pt;" width="137"> </td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl68" style="height: 21pt; width: 227pt;" width="302" height="28">0.00088750832039050400</td> <td class="xl68" style="width: 223pt;" width="297">0.00104097990908775000</td> <td class="xl68" style="width: 224pt;" width="298">0.00098577665117589100</td> <td class="xl68" style="width: 218pt;" width="290">0.00117967587001095000</td> <td class="xl68" style="width: 210pt;" width="280">0.00087081241676057800</td> <td class="xl68" style="width: 227pt;" width="302">
</td> <td class="xl68" style="width: 245pt;" width="327">
</td> <td class="xl68" style="width: 233pt;" width="310">
</td> <td class="xl68" style="width: 231pt;" width="308">
</td> <td class="xl68" style="width: 103pt;" width="137">
</td> <td class="xl68" style="width: 103pt;" width="137">
</td> <td class="xl68" style="width: 103pt;" width="137">
</td> </tr> </tbody></table>

 

[vbanovice123]

I think I know why. I am not sure how to fix this?

There are blanks in the last two rows in the OLD DATA sheet and the NEW DATA sheet has values like below:

NEW DATA

<table border="0" cellpadding="0" cellspacing="0" width="361"><col style="width: 271pt;" width="361"> <tbody><tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 271pt;" width="361" height="28">0.00041407867494824000</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl66" style="height: 21pt; width: 271pt;" width="361" height="28">0.00063201481965784000</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 271pt;" width="361" height="28">0.00154734662743816000</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl68" style="height: 21pt; width: 271pt;" width="361" height="28">0.00074098289201264000</td> </tr> </tbody></table>

OLD DATA


<table border="0" cellpadding="0" cellspacing="0" width="302"><col style="width: 227pt;" width="302"> <tbody><tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 227pt;" width="302" height="28">0.00041407867494824000</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl66" style="height: 21pt; width: 227pt;" width="302" height="28">0.00063201481965784000</td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl67" style="height: 21pt; width: 227pt;" width="302" height="28"> </td> </tr> <tr style="height: 21pt;" height="28"> <td class="xl68" style="height: 21pt; width: 227pt;" width="302" height="28">
</td> </tr> </tbody></table>

 

[vbanovice123]

I think I should do something like this:

If NewDataSheet.Cell.Value is like 0 then
If OldataSheet.Cell.Value is like "" (blank) then fill it with 0

I am not certain about the syntax and whether this is a good approach. Please advise.