Numeric compare fails using cells.value

[MD975]

I need to compare two workbook sheets. If the rows are identical, I ignore. If the data on the row is different I flag the "New" workbook row as modified. If the row does not exist on the "Old" workbook sheet, I flag the "New" workbook as new. The "Old" is extracted from a Sharepoint Workbook (W7 Excell 2010), and the intent is to know what rows need to be modified and what rows need to be updated to put back into Sharepoint. I tried the trick of TRANSPOSE(TRANSPOSE) and even though it looked like it was working, it never found matching rows. Thought that it might be because of empty cell versus spaces and how these two workbooks were created differently (New is xls 2003/2007, sharepoint is the Old and is xlsx). So I gave up on that.
The first column is reserved for my Flag indicator. The second column has a sorted Alphanumeric Key, so if they match I do the balance of the columns (total of 49).
My problem with the logic below, is the Cells.Value does not test as equal for all of the cells that contain numeric values, even though they should. In my searches, I think this is because of the internal storage of numeric values that are floating (0.9999999999 could be a 1). I changed the Advanced Excell Options for both Workbooks to "set precission as displayed", but it still doesn't work. Running Macro on W7 Excel 2007. The Do Whiles work because of the Alphanumeric in column B, it is the IF statement that does not work when the cells contain numbers.

Thanks in advance for the help

Oldx = 2
Newx = 2
Do While Newx < LastRowNew + 1
Status = 1
Do While Workbooks(WkBookNew).Sheets(WkSheetNameNew).Cells(Newx, 2).Value < _
Workbooks(WkBookOld).Sheets(WkSheetNameOld).Cells(Oldx, 2).Value
Workbooks(WkBookNew).Sheets(WkSheetNameNew).Cells(Newx, 1).Value = "New"
Newx = Newx + 1
Loop
Do While Workbooks(WkBookNew).Sheets(WkSheetNameNew).Cells(Newx, 2).Value > _
Workbooks(WkBookOld).Sheets(WkSheetNameOld).Cells(Oldx, 2).Value And _
Oldx < LastRowOld
Workbooks(WkBookOld).Sheets(WkSheetNameOld).Cells(Oldx, 1).Value = "Not"
Oldx = Oldx + 1
Loop
If Workbooks(WkBookNew).Sheets(WkSheetNameNew).Cells(Newx, 2).Value = _
Workbooks(WkBookOld).Sheets(WkSheetNameOld).Cells(Oldx, 2).Value Then
Status = 1
For j = 3 To LastColumnNumber
If Workbooks(WkBookNew).Sheets(WkSheetNameNew).Cells(Newx, j).Value <> _
Workbooks(WkBookOld).Sheets(WkSheetNameOld).Cells(Oldx, j).Value Then
Status = 2
End If
Next j
Else
Status = 3
End If
Newx = Newx + 1
Loop

 

[AlphaFrog]

The "precision as displayed" option should work if both workbooks have the same display settings.

Do you know it is a numeric precision issue or is that speculation? If there are case differences with text comparisons, then the two will not match as well e.g.; "Text" <> "text"

 

[MD975]

Yes, I don't know why "precision as displayed" does not work, maybe I'll try running the macro on 2010. Yes the numeric precision issue is my speculation (after searching the net). This Old Workbook was developed from a previous version of the New workbook. So I'm not expecting a case issue with Alpha. Each Month we have a new version of the New that we have to add or modify the Old. If there is a case issue, then I'll change the Old so next month that will match.

 

[AlphaFrog]

Yes, I don't know why "precision as displayed" does not work, maybe I'll try running the macro on 2010. Yes the numeric precision issue is my speculation (after searching the net). This Old Workbook was developed from a previous version of the New workbook. So I'm not expecting a case issue with Alpha. Each Month we have a new version of the New that we have to add or modify the Old. If there is a case issue, then I'll change the Old so next month that will match.

Maybe try something like this to compare numbers or text.

    [COLOR=darkblue]Dim[/COLOR] wsOld [COLOR=darkblue]As[/COLOR] Worksheet, wsNew [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Set[/COLOR] wsOld = Workbooks(WkBookOld).Sheets(WkSheetNameOld)
    [COLOR=darkblue]Set[/COLOR] wsNew = Workbooks(WkBookNew).Sheets(WkSheetNameNew)
    Oldx = 2
    Newx = 2
    [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] Newx < LastRowNew + 1
        Status = 1
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] wsNew.Cells(Newx, 2).Value < wsOld.Cells(Oldx, 2).Value
            wsNew.Cells(Newx, 1).Value = "New"
            Newx = Newx + 1
        [COLOR=darkblue]Loop[/COLOR]
        [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]While[/COLOR] wsNew.Cells(Newx, 2).Value > _
                 wsOld.Cells(Oldx, 2).Value And _
                 Oldx < LastRowOld
            wsOld.Cells(Oldx, 1).Value = "Not"
            Oldx = Oldx + 1
        [COLOR=darkblue]Loop[/COLOR]
        [COLOR=darkblue]If[/COLOR] wsNew.Cells(Newx, 2).Value = wsOld.Cells(Oldx, 2).Value [COLOR=darkblue]Then[/COLOR]
            Status = 1
            [COLOR=darkblue]For[/COLOR] j = 3 [COLOR=darkblue]To[/COLOR] LastColumnNumber
                [B][COLOR=darkblue]If[/COLOR] Trim(LCase(Format(wsNew.Cells(Newx, j).Value, "0.00"))) <> _
                   Trim(LCase(Format(wsOld.Cells(Oldx, j).Value, "0.00"))) [COLOR=darkblue]Then[/COLOR][/B]
                    Status = 2
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]Next[/COLOR] j
        [COLOR=darkblue]Else[/COLOR]
            Status = 3
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        Newx = Newx + 1
    [COLOR=darkblue]Loop[/COLOR]

 

[AlphaFrog]

I just noticed that you don't use the variable Status for each row. You set it to a value, but don't use it.

 

[MD975]

I didn't give you all of the logic, there is a Case instruction before the last loop. I've tried on 2010, made sure the precision was set and the results are the same. I will modify and try the trim(lcase logic above) and let you know. Thanks

 

[MD975]

The logic
If Trim(LCase(Format(wsNew.Cells(Newx, j).Value, "0.00"))) <> _
Trim(LCase(Format(wsOld.Cells(Oldx, j).Value, "0.00"))) Then

Worked

Thanks

 

[AlphaFrog]

You're welcome. Glad it worked.

You could exit out of the j loop once the Status set.

    [color=darkblue]For[/color] j = 3 [color=darkblue]To[/color] LastColumnNumber
        [color=darkblue]If[/color] Trim(LCase(Format(wsNew.Cells(Newx, j).Value, "0.00"))) <> _
           Trim(LCase(Format(wsOld.Cells(Oldx, j).Value, "0.00"))) [color=darkblue]Then[/color]
            Status = 2
            [color=darkblue]Exit[/color] [color=darkblue]For[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] j

 

[MD975]

OK, I'll do that.

Now that it is working, with this construct (wsOld.Cells(Oldx, j).) can I set that cells backgound color. Something like

wsOld.Cells(Oldx, j).Select.Interior.Color = ???

 

[AlphaFrog]

Sorry no. Close

No need to .Select

wsOld.Cells(Oldx, j).Interior.Color = ???