CODEWITHSUNDEEP
vbaexcel
Rita
Rita

Reputation: 1237

VBA code If condition not satisfying, Stlll entering the loop

I have IF Condition as below. Here EntitySum is 1. Though the condition is not satisfying, it is enterign into If loop.

    Dim RowCount As Integer
Dim ColCount As Integer
Dim m, x As Integer
Dim EntitySum As Double
RowCount = Worksheets("Contribution").UsedRange.Rows.Count - 4
ColCount = Worksheets("Entities").UsedRange.Rows.Count - 4

m = 4

'outer loop for Rows
Do
EntitySum = 0
x = 6
m = m + 1
    'inner loop for Columns
    Do
    x = x + 1
    EntitySum = EntitySum + Worksheets("Contribution").Cells(m, x).Value
    Loop Until x = ColCount + 6
    If EntitySum <> 1 Then
        MsgBox "The Entity Contribution at Row " & m & " is not 100%. Please Fix it to proceed further."
        Exit Sub
    End If
Loop Until m = RowCount + 4

enter image description here

Is it any issue with VBA?

Appreciate your responses.

Thanks, Rita

Upvotes: 1

Views: 67

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The problem is even though your screenshot shows that the value of EntitySum = 1, it is not. Here is a simple way to replicate your problem

In cell A1 type this number 0.00000000000000001. You will see that Excel automatically formats it as 1E-17

Now Try this code

Sub Sample()
    Dim EntitySum As Double

    EntitySum = 1 + Range("A1")

    If EntitySum <> 1 Then
        MsgBox "A"
    End If
End Sub

Now EntitySum is definitely not equal to 1 but if you hover your mouse over that variable, it will show it as 1. It is a floating point issue. Excel is not able to handle such large numbers and hence it gives a problem.

To understand why it is behaving like that in your case, you will have to check the cells Worksheets("Contribution").Cells(m, x).Value and see what kind of values do they have.

enter image description here

Upvotes: 1

Related Questions