VBA For Loop with IF Statement and Calculation

[mjohnston0209]

I am new to VBA and am trying to create a For Loop for a designated range (Cells D2:O10).

Each month, I have to remove the percentages in the current month and reallocate the existing percentages to retotal 100%. For example, if the percentages of row 2 totals 90% (Column C) after removing the current month's percentages, I divide the remaining percentage in each cell for that row by 90% to once again total 100%. Then the macro will move to the next row and repeat.

To make it more confusing, I want the loop to skip cells without a task number (Column A) or when the remaining budget (Column B) is $0. I do not want a value entered into those cells so that it is easier to identify which cells have an actual amount allocated to them. My current macro will enter zero into these cells and then remove them at the end with a Find and Replace (Not sure if that is the easiest way to accomplish this task).

I cannot only analyze certain rows with tasks because the existing spreadsheet pulls the tasks from another tab. If a task appears to be empty, it actually is not. That is why I predefined the range in my macro.

The recalculated percentages are then multiplied by the remaining budget on a different spreadsheet, which shows how the budget will be spent throughout the year.

The problem I am having is that I use the ActiveCell.Row to identify which row I am analyzing. However, the active row remains constant based on the cell selected before the macro begins.

Any help would be greatly appreciated!

My macro for the loop, which is not even close to being right is as follows:


Sub Respreader()

' Identify rows 2 thru 11
For x = 2 To 11
' Identify columns D thru O
For y = 4 To 15

If Range("A" & ActiveCell.Row) = "" Then
Cells(x, y).Value = 0
ElseIf Range("B" & ActiveCell.Row) = 0 Then
Cells(x, y).Value = 0
ElseIf IsEmpty(ActiveCell) Then
Cells(x, y).Value = 0
Else
Cells(x, y).Value = Round(Cells(x, y) / Range("C" & ActiveCell.Row), 3)
End If

Next y
Next x

End Sub

Once again Column A = Task Number, Column B = Remaining Budget, Column C = Spread Total, and D thru O represents each month. I cannot post an attachment so sorry for the wordy explanation.

 

[NARAYANK991]

Hi ,

I don't think I have understood your requirement , but see if this does what you want :

Sub Respreader()
    Dim i As Integer
    
    For x = 2 To 11
        For y = 4 To 15
            If Cells(x, 1).Value = "" Then
               Cells(x, y).Value = 0
            ElseIf Cells(x, 2).Value = 0 Then
               Cells(x, y).Value = 0
            ElseIf IsEmpty(Cells(x, 1).Value) Then
               Cells(x, y).Value = 0
            Else
               Cells(x, y).Value = Round(Cells(x, y).Value / Cells(x, 3).Value, 3)
            End If
        Next y
    Next x
End Sub

 

[mjohnston0209]

Even with a vague understanding, you figured out exactly what I needed. Thanks!