excel - If a cell in Column 7 equals the value in another cell change to the value in another cell

Question

The code below changes the value of any cell in column 7 whose value is equal to the value in AF14 and changes it to the value in AF15. I would like to add more criteria say if is AF16 then change to A17, if is AF17 then change to AF18 and so on.

Thanks in advance

Private Sub macro13()

Dim i As Integer
Dim WK As Worksheet
Dim rg As Range

Set WK = Sheet4
For i = Cells(Rows.Count, 7).End(xlUp).Row To 1 Step -1
    If Cells(i, 7) = rg Then Cells(i, 7).Value = Range("AF15").Value
Next i

End Sub

question from:https://stackoverflow.com/questions/65863165/if-a-cell-in-column-7-equals-the-value-in-another-cell-change-to-the-value-in-an

Answer 1

Try something like this:

Private Sub macro13()

Dim i As Integer
Dim WK As Worksheet
Dim rg As Range
Dim cell_arr(1 To 5, 1 To 2) As String

Set WK = ActiveSheet

' cell_arr(x,1) --> cell_arr(x,2)
cell_arr(1, 1) = "AF14": cell_arr(1, 2) = "AF15"
cell_arr(2, 1) = "AF16": cell_arr(2, 2) = "AF17"
cell_arr(3, 1) = "AF18": cell_arr(3, 2) = "AF19"
cell_arr(4, 1) = "AF20": cell_arr(4, 2) = "AF21"
cell_arr(5, 1) = "AF22": cell_arr(5, 2) = "AF23"

For k = 1 To UBound(cell_arr, 1)
    For i = Cells(Rows.Count, 7).End(xlUp).Row To 1 Step -1
    
        If Cells(i, 7) = Range(cell_arr(k, 1)) Then
                Cells(i, 7).Value = Range(cell_arr(k, 2)).Value
        End If
    
    Next i
Next k

End Sub