(Solved) – How do I keep initial value in a for loop?

  • by

I am trying to write code in Excel VBA, and I need the initial numeric value to stay the same until the string value changes, so that the last numeric value will be subtracted from the initial numeric value. After the string value changes, then a new initial numeric value is needed.

More specifically:
For a given stock ticker, I need to keep the opening price at the beginning of the year, and subtract it from the closing price at the end of the year, to find the yearly change. When the ticker changes, I then need a new beginning of the year opening price for the new stock. Unfortunately, the for loop I have written does not keep the same beginning of the year opening price, but instead subtracts the year end closing price from that same day’s opening price (because they are in the same row i). I can’t hard code the opening price, because it needs to change when the ticker changes.

Here is the code:

'Loop through all stock trades
For i = 2 To LastRow

    ' Set opening price
    Dim Opening_Price As Double
    Opening_Price = Cells(i, 3).Value

    ' Check if we are still within the same ticker.
    If Cells(i   1, 1).Value <> Cells(i, 1).Value Then

      ' Set the ticker name
      Ticker = Cells(i, 1).Value

      ' Print the ticker name in the Summary Table
      Range("I" & Summary_Table_Row).Value = Ticker

      ' Add or subtract closing price from first opening price
      Yearly_Change = Cells(i, 6).Value - Opening_Price

      ' Print the change in stock price in the Summary Table
      Range("J" & Summary_Table_Row).Value = Yearly_Change

      ' Calculate percent change from opening to closing price
      Percent_Change = (Yearly_Change / Opening_Price) * 100

      ' Print the percent change in the summary table
      Range("K" & Summary_Table_Row).Value = Percent_Change

      ' Add one to the summary table row
      Summary_Table_Row = Summary_Table_Row   1

    End If

Next i

Leave a Reply

Your email address will not be published. Required fields are marked *