Need help transforming my code

[ouadad]

I guess I've just been using For-Next loops because they were easy to set up, but now I've come across a problem that requires a more sophisticated approach and I'm at a loss to how to set it up. Below is the code I've been using.

It works by finding a specific year and then captures and stores a string value (sn) from column 1 and an integer value (points) from column 3 (out of 234678 values). Then it compares the value in sn to one of 112345 similar values in column 16. If the string values match, it pastes the points value into the column beside it.

So right now, it compares each of 234678 string values to each 112345 string values.

I thought about using a While-Wend loop, but I'm not sure how to set it up with the comparison and I'm looking for a little assistance.

Sub Tier1_Points_Used_Per_Year()
Dim irow1 As Long, irow2 As Long, fy As Integer, sn As String, points As Integer
    For irow1 = 2 To 234678
        If Cells(irow1, 2) = 2018 Then
            sn = Cells(irow1, 1)
            points = Cells(irow1, 3)
            For irow2 = 2 To 112345
                If Cells(irow2, 16) = sn Then
                    Cells(irow2, 17) = points
                End If
            Next irow2
        End If
    Next irow1
End Sub

Here is a sample of the data for the year 2013. The data is sorted by sn in both columns.

sn	FY	Total_Points		Starting Points	2013-sn	2013-points
A10009044	2015	146		300	A10009044	
A10009044	2018	81		300	A10023027	
A10009044	2019	433		300	A10036355	
A10023027	2014	150		300	A10040199	
A10036355	2013	485		300	A10047268	
A10036355	2014	136		300	A10048544	
A10036355	2015	191		300	A10052525	
A10036355	2018	126		300	A10055317	
A10040199	2015	197		300	A10055507

 

[Fluff]

How about

Sub ouadad()
   Dim Ary As Variant, Oary As Variant
   Dim i As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp).Offset(, 2)).Value2
   Oary = Range("P2", Range("P" & Rows.Count).End(xlUp).Offset(, 1)).Value2
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(Ary)
         If Ary(i, 2) = 2018 Then .Item(Ary(i, 1)) = Ary(i, 3)
      Next i
      For i = 1 To UBound(Oary)
         Oary(i, 2) = .Item(Oary(i, 1))
      Next i
   End With
   Range("P2").Resize(UBound(Oary), 2).Value2 = Oary
End Sub

 

[ouadad]

That's incredible. I don't understand what you did but I will be studying it for future reference. Thank you!!!

 

[Fluff]

You're welcome & thanks for the feedback.

To learn more about dictionaries & arrays, have a look here
https://excelmacromastery.com/vba-dictionary/
https://excelmacromastery.com/excel-vba-array/