I am new to using VBA and not totally comfortable with Excel yet.

I have a project where I need to multiply a range of cells with a value that a user inputs in a table.

So if they input the year "2017" in a cell, then the value they input from that year need to be multiplied with another number from 2017, 2018, and 2019. The year they input is different, but all numbers need to be multiplied up until 2019 always.

I have attached a picture of how the excel sheet would sort of look like. The idea is that when another table to the right is added below the others, that the "price in 2019" would be calculated automatically. The calculation is included at the top of the screenshot for clarity.

I have tried creating a VBA code but it only multiplies the initial value with each value individually, instead of creating a single total number. I attached a copy of the VBA code I tried.

 Sub calculate ()
ActiveCell.Offset(0,1).Select
Dim Cell As Range
    For Each Cell In Range("F5:F24")
    If Cell.Value < Range("B7").Value Then
    
    ElseIf Cell.Value >= Range("B7") Then
        Cell.Offset(0,4).Value = Range("B8") * Cell.Offset(0,1).Value
        
        End If
    Next Cell

End Sub

If anyone can figure it out, I would appreciate it.Thank you.

CodePudding user response：

You can get by with a formula without programming:

=B8 * Product(Filter($G$5:$G$24, $F$5:$F$24>=B7, 0))

Here:

• B7 is a starting year
• B8 is a starting price
• F5:F24 is a Year column
• G5:G24 is a Price column

The formula is made for D7, so put it there, then copy D7 to D12 to update starting year and price addresses.

CodePudding user response：

Thank you for the answer. I appreciate it. But I am trying to build something that automatically calculates it when another investment is added. So no copy and pasting when a new year etc is added by the user