VBA calculates numbers incorrectly (basic arithmetic)

[zack8576]

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]

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]

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]

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!

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]

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.

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]

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

 

[Alex Blakenburg]

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]

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.

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

 

[zack8576]

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

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

thank you Fluff !

 

[Alex Blakenburg]

You're welcome. Glad we could help.