VBA use for each loop to divide through a range

Question

I have a range of values (estimated project hours) and I have a status cell (total estimated project hours. I have date columns that are being generated with vba as well. Essentially, I want to divide each cell in the range by the status total estimated hours cell, and repeat the value across that row for each date (giving estimated hours per week). After that row is complete, I want to drop to the next cell in the range and repeat the process.

Here is my code so far:

Sub headers()
    Dim start As Double
    Dim weeks As Integer

    start = Range("B1").Value
    weeks = Range("B3").Value
    For i = 0 To weeks - 1
        Cells(8, 5 + i).Value = start + 4 + (i * 7)
    Next

End Sub


Sub PopulateValues()
    Dim weeks As Integer
    Dim rRng As Range
    Dim rCell As Range

    weeks = Range("B3").Value
    resources = Range("B6").Value
    Set rRng = Range("D9:D50")

    For Each rCell In rRng.Cells

        If IsEmpty(rCell.Value) Then Exit For

        For i = 0 To weeks - 1
            Cells(9, 5 + i).Value = rCell.Value / weeks
        Next
    Next rCell

End Sub

What it's doing is looping through the range but it's doing all of the division on the same row, never iterating down to the next row.

Any help would be lovely.

Thanks in advance.

https://www.dropbox.com/s/l6dxa9ethbsix58/VBA.PNG?dl=0

for this part Cells(9, 5 + i).Value = rCell.Value / weeks since you don't ever change the "9" won't it always be filling in cells in row 9? — garbb

Rossle Rossle · Accepted Answer · 2018-07-26T22:00:52

So in your for loop you have Cells(9, 5 + i).Value. The variable in this is the "i" for the columns.

You need another variable For Each rCell In rRng.Cells

    If IsEmpty(rCell.Value) Then Exit For
    For j = 0 to weeks - 1
        For i = 0 To weeks - 1
            Cells(9+j, 5 + i).Value = rCell.Value / weeks
        Next
    Next
Next rCell

It will start with the first row, 9 (9 + j = 9), and after it runs through all columns (i), it will go to the next j ( row 10).

VBA use for each loop to divide through a range

1 Answers