CODEWITHSUNDEEP
vbaexcel
Greg
Greg

Reputation: 7

adding variables into another variable vba

Dim x As Long
Dim y As Long
Dim CDTotal As Double
Dim CSTotal As Double
Dim ETotal As Double
Dim FTotal As Double
Dim HTotal As Double
Dim ITotal As Double
Dim ITTotal As Double
Dim MTotal As Double
Dim TTotal As Double
Dim UTotal As Double
Dim TotalValue As Double
For y = 3 To 3
For x = 600 To 1 Step -1
 If Cells(x, y).Value = "CD Sector Average" Then
        CDTotal = Cells(x, y + 5).Value
        End If
    If Cells(x, y).Value = "CS Sector Average" Then
        CSTotal = Cells(x, y + 5).Value
    End If
    If Cells(x, y).Value = "E Sector Average" Then
        ETotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "F Sector Average" Then
        FTotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "H Sector Average" Then
        HTotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "I Sector Average" Then
        ITotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "IT Sector Average" Then
        ITTotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "M Sector Average" Then
        MTotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "T Sector Average" Then
        TTotal = Cells(x, y + 5).Value
    End If

    If Cells(x, y).Value = "U Sector Average" Then
        UTotal = Cells(x, y + 5).Value
    End If
    If Cells(x, y).Value = "Total Portfolio" Then
        TotalValue = UTotal + TTotal + MTotal + ITTotal + ITotal + HTotal + FTotal + ETotal + CSTotal + CDTotal
        Cells(x, y + 5) = TotalValue
    End If
Next x
Next y

There are values in the cells 5 columns to the right of these names. And I need to add them up, without adding any of the numbers in between the rows.

I am writing the last part wrong? It comes out as 0. but there is clearly data in the referenced cells.

Upvotes: 0

Views: 2256

Answers (1)

SeanC
SeanC

Reputation: 15923

I can't see anything wrong with the code, as long as your text is in column C, and the values are in column H

I've also taken the liberty of rewriting the code to make it clearer:

Sub test()
Dim x As Long
Dim y As Long
Dim TotalValue As Double

TotalValue = 0
y = 3

For x = 600 To 1 Step -1
    Select Case Cells(x, y).Value
        Case "CD Sector Average", "CS Sector Average", _
                "E Sector Average", "F Sector Average", _
                "H Sector Average", "I Sector Average", _
                "IT Sector Average", "M Sector Average", _
                "T Sector Average", "U Sector Average"
            TotalValue = TotalValue + Cells(x, y + 5).Value
        Case "Total Portfolio"
            Cells(x, y + 5).Value = TotalValue
            TotalValue = 0
    End Select
Next x

End Sub

Upvotes: 1

Related Questions