CODEWITHSUNDEEP
vba
JungleDiff
JungleDiff

Reputation: 3485

Selecting specific rows in Excel and perform calculations using VBA

I have this spreadsheet: enter image description here

For each of the sectors (IT, Consumer Discretionary, Financials, etc), I want to add % invested and return the result summed values. For example, IT should return 7%(1+2+4) and Health Care should return 24%(7+8+9).

This is what I have so far:

Dim sector_array As Variant
Dim sector As Variant
Dim ocell As Range
Dim rng As Range
sector_array = Array("Information Technology", "Financials", "Consumer 
Discretionary", "Energy", "Materials", "Consumer Staples", "Health Care", 
"Industrials", "Utilities", "Telecommunication Services", "Real Estate")


For Each sector In sector_array
For Each ocell In Range("C:C")
    If ocell.Value = sector Then
        If rng Is Nothing Then
            Set rng = ocell.EntireRow
        Else
            Set rng = Union(rng, ocell.EntireRow)
        End If
    End If
Next ocell
If Not rng Is Nothing Then
    rng.Select

End If
Next sector

How would I achieve this please?

Upvotes: 0

Views: 44

Answers (2)

QHarr
QHarr

Reputation: 84465

Use a dictionary. The keys are the sector and values the percentages. If a key exists then add the percentage to the existing value.

Option Explicit
Public Sub test()
    Dim lastRow As Long, dict As Object, i As Long, key As Variant

    With ActiveSheet
        Dim dataArr()
        dataArr = .Range("C2:E" & .Cells(.Rows.Count, "C").End(xlUp).Row).Value
    End With

    Set dict = CreateObject("Scripting.Dictionary")

    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        If Not dict.exists(dataArr(i, 1)) Then
            dict.Add dataArr(i, 1), dataArr(i, 3)
        Else
            dict(dataArr(i, 1)) = dict(dataArr(i, 1)) + dataArr(i, 3)
        End If
    Next i

   For Each key In dict.keys
       Debug.Print key & " : " & dict(key)
   Next key

End Sub

Result in immediate window (Ctrl + G)

Data

Upvotes: 0

madrac
madrac

Reputation: 112

I would do an SUMIF formula specifying each category in the column F.

Upvotes: 2

Related Questions