Open closing balance

[Zaibass]

Hi All,
I am new to VBA
looking for an automated solution to calculate opening/closing balances for each month (0-11), logic is based on formula example below (for moth 0 qty OH - usage +open PO's = closing balance for Month 0 and opening for month 1 and so on...

was trying to write VBA, however, stuck....



code

Thanks !

 

[ExcelGzh]

I'm not sure why you've started at the bottom of the sheet. Wouldn't it be better to start at the top?

Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
Lastrow = .Cells(Rows.Count,2).End(xlUp).Row
For VarianceRow = 1 to Lastrow
  'Find VarianceRow
  If .Cells(VarianceRow,3)="Variance" Then
    For MonthColumn = 5 to 17
     .Cells(VarianceRow,MonthColumn)=.Cells(VarianceRow-2,MonthColumn)-.Cells(VarianceRow-3,MonthColumn+.Cells(VarianceRow-1,MonthColumn
    Next MonthColumn
  End if
Next VarianceRow

 

[Zaibass]

Thanks for you response and proposal, as i mentioned earlier i am new to VBA so excuse me
However, in your solution there is an issue:
Form Month 0 or MonthColumn = 5 to 5 it's doing what it suppose to do, however, month 1-11 or MonthColumn=6 to 17 OH qty should be taken from previous months variance cell

say month 0 its calculating variance = 6 which is fine
But for month 1 it should be Variance(Month 0) -Monthly usage (Monty usage month 1)+open PO's (Month 1)
6-143+0 =-137 (opening balance for month 2) and so on...

where in your macro as OH qty is using same row E3:P3

this is what i came with... doing the job but dirty code...

I'm not sure why you've started at the bottom of the sheet. Wouldn't it be better to start at the top?

Set MySheet = ThisWorkbook.Worksheets("Sheet1")
With MySheet
Lastrow = .Cells(Rows.Count,2).End(xlUp).Row
For VarianceRow = 1 to Lastrow
  'Find VarianceRow
  If .Cells(VarianceRow,3)="Variance" Then
    For MonthColumn = 5 to 17
     .Cells(VarianceRow,MonthColumn)=.Cells(VarianceRow-2,MonthColumn)-.Cells(VarianceRow-3,MonthColumn+.Cells(VarianceRow-1,MonthColumn
    Next MonthColumn
  End if
Next VarianceRow

 

[Zaibass]

I have added few twist to your original code and now it's doing exactly what i was looking for !

Thank you Sir for lesson !

Sub Calculate()
Set MySheet = ThisWorkbook.ActiveSheet
With MySheet
Lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
For VarianceRow = 1 To Lastrow
'Find VarianceRow
If .Cells(VarianceRow, 3) = "Variance" Then
For MonthColumn = 5 To 5
.Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow - 2, MonthColumn) - .Cells(VarianceRow - 3, MonthColumn) + .Cells(VarianceRow - 1, MonthColumn)
Next
For MonthColumn = 6 To 16
.Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow, MonthColumn - 1) - .Cells(VarianceRow - 3, MonthColumn) + .Cells(VarianceRow - 1, MonthColumn)
Next MonthColumn

End If
Next VarianceRow
End With

End Sub

 

[ExcelGzh]

Other than the fact that you don't need the first For-Next loop of 5 to 5 - just put 5 in the code for Month - it looks pretty good.

Sub Calculate()
  Set MySheet = ThisWorkbook.ActiveSheet
  With MySheet
    Lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
    For VarianceRow = 1 To Lastrow
      'Find VarianceRow
      If .Cells(VarianceRow, 3) = "Variance" Then
        .Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow - 2, 5) - .Cells(VarianceRow - 3, 5) + .Cells(VarianceRow - 1, 5)
        For MonthColumn = 6 To 16
          .Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow, MonthColumn - 1) - .Cells(VarianceRow - 3, MonthColumn) + .Cells(VarianceRow - 1, MonthColumn)
        Next MonthColumn
      End If
    Next VarianceRow
  End With
End Sub

 

[Zaibass]

Other than the fact that you don't need the first For-Next loop of 5 to 5 - just put 5 in the code for Month - it looks pretty good.

Sub Calculate()
  Set MySheet = ThisWorkbook.ActiveSheet
  With MySheet
    Lastrow = .Cells(Rows.Count, 2).End(xlUp).Row
    For VarianceRow = 1 To Lastrow
      'Find VarianceRow
      If .Cells(VarianceRow, 3) = "Variance" Then
        .Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow - 2, 5) - .Cells(VarianceRow - 3, 5) + .Cells(VarianceRow - 1, 5)
        For MonthColumn = 6 To 16
          .Cells(VarianceRow, MonthColumn) = .Cells(VarianceRow, MonthColumn - 1) - .Cells(VarianceRow - 3, MonthColumn) + .Cells(VarianceRow - 1, MonthColumn)
        Next MonthColumn
      End If
    Next VarianceRow
  End With
End Sub

Thank you Sir !