Find a loop through an array and find where the value in a cell changes-CodePudding

I am currently learning VBA and am struggling to complete the most basic of tasks... I need to be able to loop through an array of integers (such as: a, a, a, a, ab, ab, ab, etc.) and when it changes from a to ab get the integer, place it in another cell, then move down the list and repeat. With the code I have written it will move through all the changes but not place them into different cells. Can someone please help me? I am including a picture of what should happen.

What should happen

Here is my code:

Sub StockInformationCalculator()

    'Place designations here
    Dim TickerType As String
    Dim Final As Long
    
    'Create headers for variables we need to return
    Range("I1").Value = "Ticker"
    Range("J1").Value = "Yearly Change"
    Range("K1").Value = "Percent Change"
    Range("L1").Value = "Total Stock Volume"
    Range("Q1").Value = "Ticker"
    Range("R1").Value = "Value"
    Range("O2").Value = "Greatest % Increase"
    Range("O3").Value = "Greatest % Decrease"
    Range("O4").Value = "Greatest Total Volume"
    
    
    
    'Make workbook fluid by finding last row and using that in for loop
    Final = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Create for loop to return ticker type
    For c = 2 To Final
    
        If Cells(c   1, 1).Value <> Cells(c, 1).Value Then
        TickerType = Cells(c, 1).Value
        Cells(c, 9).Value = TickerType
    
        End If
    
    Next c
    
End Sub

CodePudding user response：

From what I understand you need another variable to track the number of different tickers found which you can then use to place in another column and use that counter as the row number instead of c like you were.

You also don't need the TickerType as you could just equal one cell to the other. Cut out the middle man. But if you were gonna use it later then that's fine.

See if this works for you.

Sub StockInformationCalculator()

    'Place designations here
    Dim TickerType As String
    Dim Final As Long
    Dim TickRow As Long: TickRow = 2 'Set to starting row number you want list to populate on
    
    'Create headers for variables we need to return
    Range("I1").Value = "Ticker"
    Range("J1").Value = "Yearly Change"
    Range("K1").Value = "Percent Change"
    Range("L1").Value = "Total Stock Volume"
    Range("Q1").Value = "Ticker"
    Range("R1").Value = "Value"
    Range("O2").Value = "Greatest % Increase"
    Range("O3").Value = "Greatest % Decrease"
    Range("O4").Value = "Greatest Total Volume"
    
    
    
    'Make workbook fluid by finding last row and using that in for loop
    Final = Cells(Rows.Count, 1).End(xlUp).Row
 
    'Create for loop to return ticker type
    For c = 2 To Final
        If Cells(c   1, 1).Value <> Cells(c, 1).Value Then
            TickerType = Cells(c, 1).Value
            Cells(TickRow, 9).Value = TickerType
            TickRow = TickRow   1 'Add 1 every time a different ticker is found
        End If
    Next c
    
End Sub