Rounding issue treatment different between columns

[haxor]

Good Day

I have a sheet where I add all the values in Column N and add it up in column P

I then have logic to put the value where it is bigger than 45 in column D in the last row. I then need to match that value to one of the values in Column N and then put the value of Yes in column G

All this works 100% where column N doesn't have decimal values. The problem is when there are decimal values in column N, column P and column D disregards (Rounds) this and then I don't find a match between column D and column N

Example: The total for column N = 46,5. Thus it is 1,50 > 45. The problem is the value of 2 gets written to column D and not 1,50 and column P is also incorrect as it is rounding the values as column P = current row + previous row of column N. Column P total must be 46,50 and not 47,00 There should be a Yes in column G in row 2 as the value of 1,50 is there

I have tried making column D and column P of type Number with 2 decimals and I have also tried the Round function (with 2 decimals) in VBA but it doesn't work. How can I fix this? Column N works perfectly

 

[jasonb75]

Unless you're using rounding functions in those cells, they are not automatically rounded. Decimals are displayed as per the formatting applied, but any formulas will still refer to the actual value. The only exception being the use of 'precision as displayed' from excel options.

If your numbers are not matching as expected then it would mean that they are different in some way, or that you are using the wrong functions to try and detect them. Remember 2 and "2" are not the same, numbers should never be enclosed in double quotes, preceded with an apostrophe or formatted as text unless it is absolutely necessary (account numbers with leading zeros for example).

 

[haxor]

Thanks Jason

When I manually copy for example the value of 1,50 to column D or Column P, it keeps it as 1,50 but not when my Macro runs. I also ticked 'precision as displayed' but it doesn't make a difference. And also when I clear my sheet it sets Column D and column P back to General as type even if before running my Macro I make it type Numeric

Could I try on Sheet Activate function in VBA to be of type Numeric, 2 decimals? Or what is the best place to start to troubleshoot?

 

[jasonb75]

Precision as displayed is best not used, it has the potential to fix one problem and cause many more. I only mentioned it as a possible cause of the problem that you encountered, not as something that should be tried.

Without seeing the code it is impossible to diagnose the cause, any proper numeric value should copy over correctly. I notice that you're using a comma as a decimal separator, I assume that this is correct in line with your Excel settings? File > Options > Advanced > Editing options > Decimal separator? Note that if the box for 'Use system separators' is checked then it will be based on your computers settings regardless of what the individual boxes show.

It might be possible that the method that you're using in your code doesn't recognise comma decimals as valid numbers, much (if not all) vba formatting is based on US settings. Dates can often be problematic in UK format, possibly you're encountering a similar issue with decimals. As I mentioned above, without seeing the code it is impossible to say, I can do nothing more that guess.

When posting your code please click the </> icon and paste it into the pop up window so that it formats correctly in the forum. Please do not attempt to add any formatting to your code (different font / bold / colour etc) as this tends to mess up the formatting.

 

[haxor]

I have the following:

 Dim counter063 As Integer
   Dim count063 As Integer
   Dim sum063 As Integer
   'Parameter for overtime_2020/07/26
   Dim hours063 As Integer
   'Parameter for overtime_2020/07/26
   Dim a As Integer

   counter063 = 2

   With Worksheets("063")
 
    Do While .Cells(counter063, 1).Value <> ""
         count063 = count063 + 1
         sum063 = sum063 + count063
         a = .Cells(counter063, 14).Value
         hours063 = hours063 + a

     
         .Cells(counter063, 5).Value = count063

         .Cells(counter063, 16).Value = hours063
       
       
       

       
       
 
            If .Cells(counter063, 1).Value <> .Cells(counter063 + 1, 1).Value Then
            With .rows(counter063).Borders(xlEdgeBottom)
               .LineStyle = xlContinuous
               .Weight = xlThick
            End With
            .Cells(counter063, 6).Value = sum063
           
        If .Cells(counter063, 9).Value = TextBox1.Text Then
       
            hours063 = hours063 - .Cells(counter063, 14).Value
       
         End If
           
           
             .Cells(counter063, 4).Value = hours063 - 45
           
           
             If .Cells(counter063, 4).Value > 0 Then
             .Cells(counter063, 4).Interior.ColorIndex = 3
             Else: .Cells(counter063, 4).Interior.ColorIndex = 37
             End If
           
           
            count063 = 0
            sum063 = 0

            hours063 = 0


            End If
       
       
    counter063 = counter063 + 1
   
      Loop
     
   End With

I then have a button for the final step:

Sub Button063_4_Click()


  Dim a As Variant, i As Long
  Dim dic As Object, s As String



  a = Range("A2:T" & Range("A" & rows.count).End(3).Row).Value2
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 1 To UBound(a, 1)
    dic(a(i, 1) & "|" & a(i, 14)) = i
  Next i

  For i = 1 To UBound(a, 1)
    s = a(i, 1) & "|" & a(i, 4)
     If a(i, 4) <> "" Then If dic.exists(s) Then a(dic(s), 7) = "Yes"
'        a(dic(s), 7).Interior.ColorIndex = 37
   
  Next
  Range("G2").Resize(UBound(a)).Value = Application.Index(a, , 7)





End Sub

 

[jasonb75]

Are any of the decimal values edited by the variables hours063 or sum063? If so then you would need to declare them as Double instead of Integer.

I had a quick glance at some of your earlier threads as I thought some of your code looked familiar, is this the follow up to the discussion that you were having with @DanteAmor last week?

 

[haxor]

Yes you are correct. It is the follow up with @DanteAmor

No values are edited but let me declare it as Double and see if it works

 

[haxor]

Awesome!

Thanks a lot Jason. It works. I declared hours063 and sum063 as Double

 

[jasonb75]

Integer is for whole numbers only, although it is preferable to use Long instead of Integer. It is effectively the same thing but can handle much bigger numbers. See link below for examples

Data type summary

Office VBA reference topic

docs.microsoft.com