# VBA calculates numbers incorrectly (basic arithmetic)



## zack8576 (Jan 6, 2023)

I have this VBA code that converts the quantity of an item from how many inches to how many rolls
this code is not working on a particular file, see below, there are 276 inches of this item
using this equation: ((n / 12) \ 14.5) * 2, and the returned result is 0 ?







Test file below








						SEALANT CALCULATION ISSUES.csv
					

Shared with Dropbox




					www.dropbox.com
				




code below

```
Sub SealantConseal()
    Dim lrNew As Long
    lrNew = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2

           With Range("M1", Cells(Rows.Count, "M").End(3))
            .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
            .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
           End With

    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
    lrNew = lrNew + 1
    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = ((n / 12) \ 14.5) * 2  ' \ REMOVED ALL NUMBERS AFTER DECIMAL
    Cells(lrNew, "D") = "F51019"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CS-102 Sealant"
    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete
    End If
End Sub
```

any help, or criticism on the way this code is written is welcomed.. thanks !


----------



## Domenic (Jan 6, 2023)

That's because you're using two different types of division operators, the `/ Operator` that returns a floating point result and the `\ Operator` that returns an integer result.  So, for example, let's assume that `n` is equal to 100.  As such, it would be evaluated as follows...


```
=((100 / 12) \ 14.5) * 2

=(8.33333333333333 \ 14.5) * 2

=(0) * 2

=0
```

Hope this helps!


----------



## Alex Blakenburg (Jan 7, 2023)

Did you actually give us all the code ? 
Do you have lr set to a value somewhere ? or do you have an "On Error Resume Next" in your code somewhere ?
When I run your code it actually errors out on the line "n = WorksheetFunction.SumIfs(..." because lr is 0.
(You might want to consider using Option Explicit)

If I add the line `lr = lrNew` into your code then your formula returns the value 2 for n which is what I would expect your formula result to be with an input value of 276.


----------



## zack8576 (Jan 7, 2023)

Domenic said:


> That's because you're using two different types of division operators, the `/ Operator` that returns a floating point result and the `\ Operator` that returns an integer result.  So, for example, let's assume that `n` is equal to 100.  As such, it would be evaluated as follows...
> 
> 
> ```
> ...


Domenic, so if I want to always receive integer results, no number after the decimals. I should modify the equation as :

```
Cells(lrNew, "C") = n \ 12 \ 14.5 * 2
```

This should give me the result of 2 right? I am still getting 0 when n = 276...


----------



## zack8576 (Jan 7, 2023)

Alex Blakenburg said:


> Did you actually give us all the code ?
> Do you have lr set to a value somewhere ? or do you have an "On Error Resume Next" in your code somewhere ?
> When I run your code it actually errors out on the line "n = WorksheetFunction.SumIfs(..." because lr is 0.
> (You might want to consider using Option Explicit)
> ...


Alex, lr was a global dim, I've moved Dim lr As Long into this subroutine ( I am starting to learn that global dim are not the best option...)
the result is still 0... I think I am missing something stupid here

```
Sub SealantConseal()
    Dim lrNew As Long, lr As Long
    lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    sr = 2

    With Range("M1", Cells(Rows.Count, "M").End(3))
        .Replace What:="""", Replacement:=vbNullString, LookAt:=xlPart 'REMOVE THE INCH MARK
        .Replace What:="/JOINT", Replacement:=vbNullString, LookAt:=xlPart ' REMOVE /JOINT
    End With
    n = WorksheetFunction.SumIfs(Range("M" & sr & ":M" & lr), Range("K" & sr & ":K" & lr), "*JOINT SEALANT*")
    lrNew = lrNew + 1
    Cells(lrNew, "A") = Cells(lr, "A")
    Cells(lrNew, "B") = "."
    Cells(lrNew, "C") = n \ 12 \ 14.5 * 2
    Cells(lrNew, "D") = "F51019"
    Cells(lrNew, "I") = "Purchased"
    Cells(lrNew, "K") = "CS-102 Sealant"
    If Cells(lrNew, "C").Value = 0 Then
        Rows(lrNew).Delete
    End If
End Sub
```


----------



## Fluff (Jan 7, 2023)

This line `lrNew = lrNew + 1`needs to be `lrNew = lr + 1`


----------



## Alex Blakenburg (Jan 7, 2023)

I didn't have any issue with your original calculation using first / and then \.
The "\" (integer division) does the division and then truncates the decimals off to return just the integer. You probably do not want to do that every stage of the calcuation, you just need to think through what you are trying to achieve.
You are now getting zero because *you removed the brackets*. The multiply by 2 takes precedence over the integer divide, so you are actually dividing by 14.5*2 (29) and getting a figure that is < 1 which is being truncated to zero.
Try

```
Cells(lrNew, "C") = (n / 12 \ 14.5) * 2
```

Assuming you want the result to be on the next row after the current last row you need to add in the second line below after you get the value for lr


```
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row   ' To show where to add the next line
*lrNew = lr*
```

Fluff has just given you a different option but I assumed you might be doing a loop and might want to keep the lrNew = lrNew + 1 but as you currently have it either will work.


----------



## zack8576 (Jan 7, 2023)

Alex Blakenburg said:


> I didn't have any issue with your original calculation using first / and then \.
> The "\" (integer division) does the division and then truncates the decimals off to return just the integer. You probably do not want to do that every stage of the calcuation, you just need to think through what you are trying to achieve.
> You are now getting zero because *you removed the brackets*. The multiply by 2 takes precedence over the integer divide, so you are actually dividing by 14.5*2 (29) and getting a figure that is < 1 which is being truncated to zero.
> Try
> ...


Thanks Alex for the detailed explanation, this fixed the issue thanks a bunch !


----------



## zack8576 (Jan 7, 2023)

Fluff said:


> This line `lrNew = lrNew + 1`needs to be `lrNew = lr + 1`





Fluff said:


> This line `lrNew = lrNew + 1`needs to be `lrNew = lr + 1`


thank you Fluff !


----------



## Alex Blakenburg (Jan 7, 2023)

You're welcome. Glad we could help.


----------

