How to convert ranges to arrays - nested loops - excel vba

Viewed 13

It has been a long time since I converted ranges to arrays. I have a list of 500 stock tickers in column D and associated stock names in column E. The list repeats itself every month, and I have 13 months of data. Problem is that sometimes the name changes when I add the new month's data, e.g., ATandT may become AT&T while the symbol remains "T". But the name must be consistent for all 13 months of data in the database. The following code uses two For Next loops to update the names in the prior 12 months of data with the newest names from the 13th month. It works well, but is obviously slow. If I convert the ranges to arrays, it will run much faster. Can someone give me a start with this. Thanks.

    Sub changeRows()
Dim ws As Worksheet, rw As Long, lastrow As Long, tkr As String
Dim rw2 As Long, stk As String

Set ws = ActiveSheet
     ws.Activate

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
     
    For rw2 = 11936 To lastrow
        tkr = Cells(rw2, 4)
        stk = Cells(rw2, 5)
    For rw = 2 To 11935
        If ws.Cells(rw, 4) = tkr Then
            ws.Cells(rw, 5) = stk
        End If
    Next rw
    Next rw2

End Sub

0 Answers
Related