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

Resources