Forum Discussion

JamesPhImp's avatar
JamesPhImp
Copper Contributor
Jul 27, 2023

VBA code to find and replace in each row based on cell value

Hi I need a VBA code that will cycle through a sheet and do the following. highlight the row and do a find and replace for that row, find the value in column B and replace with the value in column C...
Macros and VBA
JamesPhImp's avatar
JamesPhImp
Copper Contributor
Jul 28, 2023
yes they are
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jul 28, 2023

    JamesPhImp 

    Thanks. We'll have to loop from the bottom upwards, otherwise 230709 would be replaced with 230710, then with 230711, then with 230712 etc.

    Sub C2B()
    Dim r As Long
    Dim LastRow As Long
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    For r = LastRow To 2 Step -1
        Range("B" & r & ":B" & LastRow).Replace _
            What:=Range("B" & r).Value, _
            Replacement:=Range("C" & r).Value, _
            LookAt:=xlWhole
    Next r
    Application.ScreenUpdating = True
End Sub