adding variables into another variable vba

Question

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.

Why are you looping (and double-looping) if you're not keeping any values found or adding to an existing one? All you end up with is the final number. — Lance Roberts
– Lance Roberts, Commented Jun 22, 2015 at 19:53
And why all the if statements that save temporary values since you only want one result? — Lance Roberts
– Lance Roberts, Commented Jun 22, 2015 at 19:55
The whole inside of your loop could be one if statement with a condition ORing all strings, then assigning that value to Cells(x,8). — Lance Roberts
– Lance Roberts, Commented Jun 22, 2015 at 19:56
There is a lot of other data in the sheet. The cells I am naming are adding up data that is being pulled from a source other than excel so I couldnt have it run along with the rest of the macro. The whole macro this is related to is around 800 lines, so providing much context is hard to do in a succinct manner. — Greg
– Greg, Commented Jun 22, 2015 at 20:08
Also, I will be adding more to each of the if statements once I figure this issue. — Greg
– Greg, Commented Jun 22, 2015 at 20:11

SeanC · Accepted Answer · 2015-06-22 20:17:02Z

1

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

answered Jun 22, 2015 at 20:17

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Greg Over a year ago

Will this still work if Total Portfolio is at the bottom? I am still getting the Total as 0. Since its looking from the bottom to the top doesn't run the Total first and then it never puts the value into the right cell?

Greg Over a year ago

I had it running with a -1 step because I have another macro that is looking for the same values and inserting rows above, so I had to run it backwards. We are A-OK thanks Sean.

Collectives™ on Stack Overflow

adding variables into another variable vba

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related