Reputation: 15
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
Upvotes: 0
Views: 1521
Reputation: 40
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).
Upvotes: 1