How to loop through a column on one sheet and find value and replace with value on another in vba-CodePudding

Problem: change value in a column on sheet1 to matching value with associated key in column on sheet 2.

sheet 1:

A
71.81
68.07
68.07
...

Sheet2: Key:value pair:

A(Key)  B(value)
0   50
1   51.37
2   52.69
3   54.04
4   55.43
5   56.85
6   58.31
7   59.8
8   61.33
9   62.9
10  64.52
20  68.07
30  71.81
40  75
50  79.39
60  83.76
70  88.36
80  93.22
90  98.35
100 100
110 100
120 100
130 100
140 100
150 100
160 100
170 100
180 100
190 100
200 100
201 100

``
-----

expected outcome: 
30
20
20

I have some fall on my face attempts that is not worth mentioning.

CodePudding user response：

Replace with Matches

For simplicity's sake, it is assumed that the ranges have at least two rows of data.

Sub ReplaceWithMatches()
    
    ' 1.) Read
    
    ' Workbook
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Sheet2")
    Dim srg As Range
    Set srg = sws.Range("A2", sws.Cells(sws.Rows.Count, "B").End(xlUp))
    Dim slrg As Range: Set slrg = srg.Columns(2) ' lookup
    Dim svData() As Variant: svData = srg.Columns(1).Value ' value
    
    ' Destination
    Dim dws As Worksheet: Set dws = wb.Worksheets("Sheet1")
    Dim drg As Range
    Set drg = dws.Range("A2", dws.Cells(dws.Rows.Count, "A").End(xlUp))
    Dim dData() As Variant: dData = drg.Value ' lookup & value
    
    ' 2.) Modify
    
    ' Loop
    Dim srIndex As Variant ' could be an error value
    Dim dr As Long
    For dr = 1 To UBound(dData, 1)
        srIndex = Application.Match(dData(dr, 1), slrg, 0)
        If IsNumeric(srIndex) Then ' match found
            dData(dr, 1) = svData(srIndex, 1)
        'Else ' no match found; do nothing?
        End If
    Next dr
    
    ' 3.) Write
    
    ' Result
    drg.Value = dData

End Sub