Compare Worksheet Values (Text and Arithmetic)

user_10374325 0
Viewed 61

I have an excel workbook with 2 worksheets, each of them composed of 60 rows and 7 columns (first column consists of text, the rest only numbers).

I found VBA code in order to compare the values of the 2 sheets. If there is a difference, the cell is filled red, but there is an issue with the columns containing numbers:

Cell in 1st Sheet Value: 6102.75

Cell in 1st Sheet Value: 6102.75000001

--> The cell is highlighted red.

Which changes should I make in the code in order not to highlight the cell, if the numbers are equal up to 4 demical points? I think that I have to make a change in the code, to compare separately for text and numbers.

Sub compareSheets(shtBefore As String, shtAfter As String)
Dim mycell As Range
Dim mydiffs As Integer

For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange

If Not mycell.Value = ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value Then

mycell.Interior.Color = vbRed

mydiffs = mydiffs + 1

Next

'Display a message box stating the number of differences found

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtAfter).Select

End Sub

Looking forward to your comments

vba excel
2 Answers

Use IsNumeric to determine if the two values are numbers then Round to truncate decimals to 4 places.

Sub compareSheets(shtBefore As String, shtAfter As String)

    Dim mycell As Range
    Dim mydiffs As Integer

    For Each mycell In ActiveWorkbook.Worksheets(shtAfter).UsedRange
        if isnumeric(mycell.Value) and isnumeric(ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value) then

            If round(mycell.Value, 4) <> round(ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value, 4) Then

                mycell.Interior.Color = vbRed
                mydiffs = mydiffs + 1

            end if

        elseif mycell.Value <> ActiveWorkbook.Worksheets(shtBefore).Cells(mycell.Row, mycell.Column).Value then

            mycell.Interior.Color = vbRed
            mydiffs = mydiffs + 1

        end if

    Next mycell 

    'Display a message box stating the number of differences found

    MsgBox mydiffs & " differences found", vbInformation

    ActiveWorkbook.Sheets(shtAfter).Select

End Sub
Admin1

You can use the Format function define the number of decimal places you want to compare and use the IsNumeric function to check wether a value is a number or not.

Example Code:

Cell A1: 6102.75    
Cell A2: 6102.75000001

Sub test
  'Since in your example both worksheets share the same layout, you only need to check
  'if one of the values is numeric, since the other value will automaticly be the same
  If IsNumeric(Cells(1,1).Value) Then
    MsgBox (Format(Cells(1,1).Value, "0.0000") = Format(Cells(1,1).Value, "0.0000"))
  Else
    MsgBox Cells(1,1).Value = Cells(1,2).Value
  End If      
End Sub

The first parameter of Format is the value you want to format

The second parameter of Format is the string which defines the format

the the 4 zeros after the dot define the number of decimals


EDIT: Since @jeeped postet a solution using the Round Function.

Note that using the Format Function cuts the rest of the decimal places, instead of rounding.

So 6102,75 and 6102,75005 would be still equal.


EDIT2:

@jeeped improved the Format-String, so there is not even the need to check if a value is a number or a text.

Updated Example Code:

Cell A1: 6102.75    
Cell A2: 6102.75000001

Sub test
  MsgBox Format(Cells(1, 1).Value, "0.0000;@") = Format(Cells(1, 2).Value, "0.0000;@")
End Sub

Using this Format-String, the function will return the number cut to 4 decimal digits if its a number, or it returns the enire text if it's a text.

user_69039970
Related