VBA replace same text in cells/table with text in first cell of each row

[CaptnAbraham]

Greetings dear Excel specialists...

Using two VBA codes I was able to create a rather large-ish construct which has a) the filenames of a certain folder in column A (starting at A6 through A238) and b) copy the linked cells from the first row (B6:SL6) into the following rows of 7 to 238. A rough example is attached as a picture.

As a third step, which I can't figure out, I would like to replace the filename in the copied cells in each row with the value in column A of the same row.
B7:SL7 should change Filename001.xlsx into Filename002.xlsx which is in A7.
B8:SL8 should change Filename001.xlsx into Filename003.xlsx which is in A8.
B9:SL9 should change Filename001.xlsx into Filename004.xlsx which is in A9.
And so forth until last filled in cell in column A.

I have tried to look for a solution here and elsewhere on the internet but couldn't find a fitting solution. It would be much appreciated if someone could be of help.

Thanks for all the help.

CaptnAbraham

 

[Joe4]

Try this:

Sub MyReplace()

    Dim lr As Long
    Dim r As Long
    Dim fnd As String
    Dim rep As String
    
'   Set value to find (from cell A6)
    fnd = Range("A6").Value
    
    Application.ScreenUpdating = True
    
'   Loop through rows 7 to 238
    For r = 7 To 238
'       Check for a value in column A
        If Cells(r, "A") <> "" Then
'           Get the value to replace
            rep = Cells(r, "A")
'           Do find/replace for columns B:SL
            Range(Cells(r, "B"), Cells(r, "SL")).Replace what:=fnd, replacement:=rep
        End If
    Next r
    
    Application.ScreenUpdating = False
    
End Sub

 

[CaptnAbraham]

That is indeed the solution. Works like a charm!

Having this as a basis, and indeed it being simple to understand, I'll be able to use it even far into the future.

Very much appreciated, Joe4!

 

[Joe4]

You are welcome.
Glad I was able to help!