VBA: Multiplying Rows In A List By A Certain Cell & Displaying Result In Another Cell

[pnguyen05]

Hi - I am new to VBA (I just started learning last week) and is hoping someone can provide some assistance.

I need to do the following:
1. Take the percentage in each row:

• Multiply it by the Total number cell in the top left corner
• Round it to 0 decimal places
• Put the result in their respective row in the Value column.

2. Once that's done for the Total 1 row, the formula needs reset and do the same for Total 2, and Total 3.
3. It needs to be dynamic so that if a user insert or delete rows, that will be accounted for.

Not sure if this is a beginner's project but so far, all I've been able to do is get the percentages for Total 1 to add up and display the sum in the cell below the last cell on the list (which is another requirement).

Please let me know if more information is needed.

Thank you

 

[Legacy 143009]

Hi,

Can you please share a screenshot also with the desired results? I have some questions:
1. What do you mean by multiplying by each?5625000x14.25%=801562.5. Then multiply 801562.5x3%=24046,875..and so on... Like this?
OR, 5625000x14.25%=801562.5, then 5625000x3%=168750..and so on.. Then sum 801563 and 168750?

2. Do you want subtotals in any specific cell?

Please share a sample results screen.

 

[pnguyen05]

Hi,

Can you please share a screenshot also with the desired results? I have some questions:
1. What do you mean by multiplying by each?5625000x14.25%=801562.5. Then multiply 801562.5x3%=24046,875..and so on... Like this?
OR, 5625000x14.25%=801562.5, then 5625000x3%=168750..and so on.. Then sum 801563 and 168750?

2. Do you want subtotals in any specific cell?

Please share a sample results screen.

Thank you for your response. Below is updated/cleaned screenshot on how the spreadsheet should look like and more clarification:

1. The multiplication would be 5,625,000 (B2) x 14.25% (D3) = 801,563, then 5,625,000 (B2) x 3.00% (D4) = 168,750, and so on...
2. The subtotals in Step 1 should be in the Value column. So 801,563 would be in E3, then 168,750 would be in E4 and so on.
3. The Percent & Value total of Total 1 subtotals needs to always be in the cell below the very last value in the range in their respective column (in this case: D19 & E19)
4. The Percent & Value total of Total 2 subtotals needs to always be in the cell below the very last value in the range in their respective column (in this case: D25 & E25)
5. The Percent total of Total 3 is the total of Total 2 (in this case: D25) + the subtotals of Total 3 (in this case: D26:D29) and needs to always be in the cell below the very last value in the range of Total 3 in their respective columns (in this case D30).
6. The Value total of Total 3 is the total of Total 2 (in this case: E25) + the subtotals of Total 3 (in this case: E26:E29) and needs to always be in the cell below the very last value in the range of Total 3 in their respective columns (in this case E30).
7. Lastly, all this needs to be dynamic so that it'll take into account when the user adds or deletes a row.

I hope the clarification is better and thank you again for taking the time out to help with this.

 

[Legacy 143009]

Hi,

This should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  Call calculatePercentage
End Sub

Sub calculatePercentage()
  Dim i As Long, lRow As Long, subTotal As Long, percentageSum As Double, allFilled As Boolean, c As Integer
  lRow = Cells(Rows.Count, "D").End(xlUp).Row
  allFilled = True
  For i = 2 To lRow
    If Cells(i, "D").Value = "" And InStr(Cells(i, "C").Value, "Total") = 0 Then
      allFilled = False
    End If
  Next
  If allFilled Then
    With Application
    .EnableEvents = False
  
  
    Range("E2:E" & lRow).Clear
    For i = 2 To lRow
      If InStr(Cells(i, "C").Value, "Total") > 0 Then
        Cells(i, "D").Clear
      End If
    Next
    For i = 2 To lRow + 1
      If i > lRow And Not InStr(Cells(i, "C").Value, "Total") > 0 Then
        Exit For
      End If
      Cells(i, "E").Value = .WorksheetFunction.Ceiling(Cells(i, "D").Value * Cells(1, "B").Value, 1)
      percentageSum = percentageSum + Cells(i, "D").Value
      subTotal = subTotal + Cells(i, "E").Value
      If Cells(i, "D").Value = "" Then
        Cells(i, "D").Value = percentageSum
        Cells(i, "D").NumberFormat = "0.00%"
        Cells(i, "E").Value = subTotal
        If Not c > 0 Then
          percentageSum = 0
          subTotal = 0
          c = c + 1
        End If
      End If
    Next
    .EnableEvents = True
    End With
  End If
End Sub

 

[pnguyen05]

Hi,

This should work:

Private Sub Worksheet_Change(ByVal Target As Range)
  Call calculatePercentage
End Sub

Sub calculatePercentage()
  Dim i As Long, lRow As Long, subTotal As Long, percentageSum As Double, allFilled As Boolean, c As Integer
  lRow = Cells(Rows.Count, "D").End(xlUp).Row
  allFilled = True
  For i = 2 To lRow
    If Cells(i, "D").Value = "" And InStr(Cells(i, "C").Value, "Total") = 0 Then
      allFilled = False
    End If
  Next
  If allFilled Then
    With Application
    .EnableEvents = False
 
 
    Range("E2:E" & lRow).Clear
    For i = 2 To lRow
      If InStr(Cells(i, "C").Value, "Total") > 0 Then
        Cells(i, "D").Clear
      End If
    Next
    For i = 2 To lRow + 1
      If i > lRow And Not InStr(Cells(i, "C").Value, "Total") > 0 Then
        Exit For
      End If
      Cells(i, "E").Value = .WorksheetFunction.Ceiling(Cells(i, "D").Value * Cells(1, "B").Value, 1)
      percentageSum = percentageSum + Cells(i, "D").Value
      subTotal = subTotal + Cells(i, "E").Value
      If Cells(i, "D").Value = "" Then
        Cells(i, "D").Value = percentageSum
        Cells(i, "D").NumberFormat = "0.00%"
        Cells(i, "E").Value = subTotal
        If Not c > 0 Then
          percentageSum = 0
          subTotal = 0
          c = c + 1
        End If
      End If
    Next
    .EnableEvents = True
    End With
  End If
End Sub

Thank you! Looks like what I'm trying to do is a bit advance for me hah. Going to go back and re-watch some of the lessons I've been taking to get a better understanding of your code.