(Solved) – VBA scripting running slow


I am new at VBA. I need to run a code through out several worksheets (loop). However it is taking about 10 min to simply fill out a column. There is a lot of rows so it makes sense that the code will take some time, but it shouldn’t take more than 3 min.

Sub Stocks():

'Add columns where we will calculate the values
For Each ws In Worksheets

  ws.Cells(1, 9).Value = "Ticker"
  ws.Cells(1, 10).Value = "Yearly Change"
  ws.Cells(1, 11).Value = "Percentage Change"
  ws.Cells(1, 12).Value = "Total Stock Volume"

'Create a loop to search the next different value on column A and print it on Column I

Dim ticker_row As Double
ticker_row = 2

LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow

    If ws.Cells(i   1, 1).Value <> Cells(i, 1).Value Then

    ticker_symbol = ws.Cells(i, 1).Value
    ws.Range("I" & ticker_row).Value = ticker_symbol
    ticker_row = ticker_row   1

    End If

Next i

Next ws

End Sub

Leave a Reply

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