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

user_13289920 0
Viewed 79

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.stack.imgur.com/pTflD.png

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

ms-office vba excel
3 Answers

If you need to split each value on three rows, please use the next code:

Sub SplitOnThreeMonths()
 Dim sh As Worksheet, arr As Variant, arrFin As Variant, lastR As Long
 Dim i As Long, j As Long, k As Long
 
   Set sh = ActiveSheet
   lastR = sh.Range("D" & Rows.count).End(xlUp).row
   arr = sh.Range("D13:D" & lastR).Value
   ReDim arrFin(1 To UBound(arr) * 3, 1 To 1)
   k = 1
   For i = 1 To UBound(arr)
    For j = 1 To 3
       arrFin(k, 1) = arr(i, 1) / 3: k = k + 1
    Next j
   Next i
   sh.Range("E13").Resize(UBound(arrFin, 1), UBound(arrFin, 2)).Value = arrFin
End Sub

It will also allow the same Gross Domestic Product, even if happening that is not very probable. But, who knows?

user_22333080

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.

user_97581940

I'm assuming you need to insert 2 rows to make space for the divided value three times -

however if you don't need to preserve data positions in the adjacent columns, the other answers provided using arrays are better than this one:

Sub PIB()
For i = 275 To 13 Step -1
    Rows(i + 1 & ":" & i + 2).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(Cells(i, "E"), Cells(i + 2, "E")).Value2 = Cells(i, "D").Value2
Next i
End Sub

Please note I haven't qualified your ranges because I don't know what you want them to be, guessing it's the current sheet you're on.

user_64806580
Related