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.
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