#75 Database Style Compare due to formatting

[Albtross]

A Database Style Compare in 1.24.8 will not show a match for 2 identical cells, if they are formated with certain formats.

If 2 cells containing "PN" are formatted as "(@)", the FindMatch function will not see them as matching.

In the statement below:
Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn), _
LookIn:=xlValues, LookAt:=xlWhole)

Worksheet1.Cells(lSearchRow, lSearchColumn) returns "PN", even if the cell is formatted as "(@)". However, the .Find function will intepret a cell formatted as "(@)" containing "PN" as " PN ". Since "PN" != " PN " it will never find a match.

Changing the above statement as follows fixes the problem:
Set c = .Find(Worksheet1.Cells(lSearchRow, lSearchColumn).Text, _
LookIn:=xlValues, LookAt:=xlWhole)

Worksheet1.Cells(lSearchRow, lSearchColumn).Text returns " PN ", which fixes the problem.

While this is a difficult issue to describe, running a Database Style Compare on the 2 attached files will show the issue.

[Albtross]

Sorry, I meant 1.34.8 and I forgot the 2nd attachment.

[ElrondPA]

This issue also affects using alternate date formats, such as mm/dd/yyyy (rather than the default m/d/yyyy). The solution Albtross describes, of using the .Text property in the FindMatch method, seems to resolve the issue for date formats as well (as long as both spreadsheets use the same format; if the format is different, I think it's appropriate to flag it as a difference).

It's surprising that this hasn't been resolved when the report is more than two years old, and the fix seems to be very simple.

[SteveT]

Sorry, don't know why I missed this one. Will add to the next version.

Thanks,

Steve