Unable to add two numbers in Excel VBA

Question

I have the following code. I get a zero when I try to add two numbers:

Public Function getCategory(Value As String, Optional col As Long = 1) As String
   Sheets("Product Master").Select
   Range("A2").Select
   Dim I As Long
   Do Until Selection.Value = Value
   ActiveCell.Offset(1, 0).Select
   Loop
   getCategory = Selection.Value
End Function


Sub Calculate()
Dim furnitureTotal As Double
Dim furQuantity As Integer
furnitureTotal = 0
furQuantity = 0

Dim applianceTotal As Double
Dim appQuantity As Integer
applianceTotal = 0
appQuantity = 0

Dim whiteGoodsTotal As Double
Dim whiteGoodQuantity As Integer
whiteGoodQuantity = 0
whiteGoodsTotal = 0

Dim softFurTotal As Double
Dim softFurQuantity As Integer
softFurTotal = 0
softFurQuantity = 0

Dim description As String
Dim total As Double
Dim quantity As Integer

Sheets("Invoice").Select
Range("F64").Select
Do Until (ActiveCell.Value = "Finish" Or ActiveCell.Value = "")
    description = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
    quantity = Selection.Value
    ActiveCell.Offset(0, 6).Select
    total = Selection.Value
    If (getCategory(description) = "Furniture") Then
        furnitureTotal = furnitureTotal + Val(total)  <---------- this line
        furQuantity = furQuantity + quantity
        End If

    If getCategory(description) = "Electronics" Then
        applianceTotal = applianceTotal + total
        appQuantity = appQuantity + quantity
        End If

    If getCategory(description) = "White Goods" Then
        whiteGoodsTotal = whiteGoodsTotal + total
        whiteGoodQuantity = whiteGoodQuantity + quantity
        End If

    If getCategory(description) = "Soft Furnishings" Then
        softFurTotal = softFurTotal + total
        softFurQuantity = softFurQuantity + quantity
        End If

    Sheets("Invoice").Select
    ActiveCell.Offset(1, -7).Select
Loop

Sheets("Invoice").Select <---------------------- breakpoint was placed here
Range("L24").Select
Selection.Value = furQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = furnitureTotal
Range("L25").Select
Selection.Value = appQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = applianceTotal
Range("L26").Select
Selection.Value = whiteGoodQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = whiteGoodsTotal
Range("L27").Select
Selection.Value = softFurQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = softFurTotal


End Sub

I ran this code when each description belonged to furniture category. When I run it the values of all the variables remain 0. When I opened the debugger and viewed the values of variables it showed the values of variable in line:

Line:    `furnitureTotal = furnitureTotal + Val(total)`
Values:         0                  0            1750

I cannot understand why it does not add two double values. Is this related to the lifetime of variable?

Answer 1

2 things I see - you use total, but this is a keyword used in a listcolumn object, and you call getcategory repeatedly, when a vlookup could replace the code.

Here's the code, slightly rewritten:

Sub Calculate()
Dim furnitureTot As Double
Dim furQuantity As Integer
Dim applianceTot As Double
Dim appQuantity As Integer
Dim whiteGoodsTot As Double
Dim whiteGoodQuantity As Integer
Dim softFurTot As Double
Dim softFurQuantity As Integer
Dim description As String
Dim Tot As Double
Dim quantity As Integer

furnitureTot = 0
furQuantity = 0
applianceTot = 0
appQuantity = 0
whiteGoodQuantity = 0
whiteGoodsTot = 0
softFurTot = 0
softFurQuantity = 0

Sheets("Invoice").Select
Range("F64").Select
Do Until (ActiveCell.Value = "Finish" Or ActiveCell.Value = "")
    description = Application.WorksheetFunction.VLookup(ActiveCell.Value, Range("'Product Master'!A2:B9999"), 2, False) ' lookup value, rather than do it repeatedly, and use built in function
    ActiveCell.Offset(0, 1).Select
    quantity = Val(Selection.Value) ' try to get number, even if it's input as text
    ActiveCell.Offset(0, 6).Select
    Tot = Val(Selection.Value)

    Select Case description ' only 1 test needed
        Case "Furniture"
            furnitureTot = furnitureTot + Tot
            furQuantity = furQuantity + quantity
        Case "Electronics"
            applianceTot = applianceTot + Tot
            appQuantity = appQuantity + quantity
        Case "White Goods"
            whiteGoodsTot = whiteGoodsTot + Tot
            whiteGoodQuantity = whiteGoodQuantity + quantity
        Case "Soft Furnishings"
            softFurTot = softFurTot + Tot
            softFurQuantity = softFurQuantity + quantity
    End Select
    Sheets("Invoice").Select
    ActiveCell.Offset(1, -7).Select
Loop

Sheets("Invoice").Select
Range("L24").Select
Selection.Value = furQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = furnitureTot
Range("L25").Select
Selection.Value = appQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = applianceTot
Range("L26").Select
Selection.Value = whiteGoodQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = whiteGoodsTot
Range("L27").Select
Selection.Value = softFurQuantity
ActiveCell.Offset(0, 1).Select
Selection.Value = softFurTot

End Sub

Answer 2

This isn't really an answer, but I don't know how else to post this.

You can avoid the VBA all together by using the SUMIF function.

For example use the following in L24 substituting the actual code used for Furniture if it is not Furniture.

=SUMIF(F:F,"Furniture",G:G)

Using this you won't need to Calculate again for that cell, as the formula will handle it for you. You can update all the other calculated cells with similar formulas and will no longer need to run the macros.

This particular formula will give you the correct results as long as you have no rows with exactly "Furniture" in column F that shouldn't be included that have a corresponding value. If you do, just manually input a reasonable range.

As an aside your Function getCategory doesn't do anything useful. As written, it will either return the value provided (it returns the value of the selected cell if the selected cell is the same as the value provided) or loop until you finally get a run-time error for trying to select a cell that does not exist.