Search code examples
excelvbams-office

How I can make a loop to divide each cell 3 times?

I have downloaded the USA Gross Domestic Product, but this is originally by trimester and I need it my month, thus, I want to divide each cell of GDP / 3 to make my time series longer and be able to plot it by month: I want to create a loop in VBA to divide each value of GDP list 3 times, and then put it below each new value calculated:

Sub PIB()

Set lista = Range("D13:D275")

For Each cell In lista
    For i = 1 To 3
        Range("E13").Offset(i - 1, 0).Value = cell.Value / 3
    Next i
Next cell

End Sub

Nonetheless, when I run it, it divides properly but just over the 3 first cells:

https://i.sstatic.net/pTflD.png

I want to effectively divide each cell 3 times and put it each value below each other, how I can do it?

Solution

  • Is this what you are trying:

    Sub PIB()

Dim arr As Variant: arr = Range("D13:D275").Value
Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
Dim x As Long

For x = LBound(arr) To UBound(arr)
    For i = 1 To 3
        dict.Add Join(Array(arr(x, 1), x, i), "|"), arr(x, 1) / 3
    Next i
Next

Range("E13").Resize(dict.Count).Value = Application.Transpose(dict.Items)

End Sub

    Be aware of the non-explicit Range references.