How can I replace in Excel multiple values in one cell with values in a column using a macro?-CodePudding

I have zero knowledge of VBA and I'm trying to create a macro that will replace "/1", "/2", etc. in cell C17 with the values in the column AF.

So far I have this, but it only replaces the first value (i.e. "/1") and stops there. Considering that I know close to nothing about VBA, I'm surprised that this even did anything. Any help will be much appreciated. Thanks!!

Sub questiontext()

Dim InputRng As Range, ReplaceRng As Range
    Set InputRng = Range("AJ3:AJ52")
    Set ReplaceRng = Range("AF3:AF52")

Range("C17").Replace What:=InputRng, Replacement:=ReplaceRng, SearchOrder:=xlByRows, LookAt:=xlPart

End Sub

CodePudding user response：

So you want to loop down a column and perform that line of code; you can use a loop to replace within a formula:

Dim i As Long:  For i = 3 to 52
    Range("C17").Formula = Replace( Range("C17").Formula, Range("AJ" & i).Value, Range("AF" & i).Value)
Next i

Depending on your intent, you may want to save your base formula for C17 so you can reference and replace later, otherwise you'll keep modifying the same formula and possibly remove items, e.g., /1 and /17 each have "/1" and may have unintended consequences; looping in reverse would benefit this scenario, e.g.:

Dim i As Long:  For i = 52 to 3 Step -1
    Range("C17").Formula = Replace( Range("C17").Formula, Range("AJ" & i).Value, Range("AF" & i).Value)
Next i