Search Replace Offset - substitute

[dj44]

Folks,

Good day can some one help me fix my array it was working fine untill somehting happened

The numbers should be searched and replaced from the offset



I have attached my workbook as im not sure how to explain

The end result should not be incrementing - its a straight swap

this is what i get now

1, 2, 3, 4, 5, 6,7, 8, 9, 10, 11, 12, 13, 14, 15

2, 3, 3, 4, 6, 6,7, 9, 9, 20, 21, 22, 23, 24, 25

3, 4, 3, 4, 7, 6,7, 10, 9, 30, 31, 32, 33, 34, 35

4, 5, 3, 4, 8, 6,7, 11, 9, 40, 41, 42, 43, 44, 45

5, 6, 3, 4, 9, 6,7, 12, 9, 50, 51, 52, 53, 54, 55

6, 7, 3, 4, 10, 6,7, 13, 9, 60, 61, 62, 63, 64, 65

the numbers should be replacing the placeholder

1, 2, 3, 4, 5, 6,7, 8, 9, 10, 11, 12, 13, 14, 15,16

The loop is doing something to the numbers im not sure what

  
   Sub SAR()
    
    
    For Each c In Range("T5:T10")
  

     c.Value = "XX1, XX2, XX3, XX4, XX5, XX6,XX7, XX8, XX9, XX10, XX11, XX12, XX13, XX14, XX15"
     
    Next

    Call test345

    End Sub

   
   Sub test345()
   

    '----  SUBSTITUTE Placeholders
  
    arrWords = Array("XX1", "XX2", "XX3", "XX4", "XX5", "XX6", "XX7", "XX8", "XX9", "XX10", "XX11", "XX12", "XX13", "XX14", "XX15")
    
   
    For i = LBound(arrWords) To UBound(arrWords)

    For Each cel In ActiveSheet.Range("T5:T10").Cells


    ' i - 16 , start fron   16 columns to the left

    cel.Replace What:=arrWords(i), Replacement:=cel.Offset(, i - 16).Value, LookAt:=xlPart
    
    
    Next cel
    
    Next i
   


    End Sub

[p45cal]

Your placeholders aren't unique enough:
XX1, XX2, XX3, XX4, XX5, XX6,XX7, XX8, XX9, XX10, XX11, XX12, XX13, XX14, XX15
On the first loop when it's looking for XX1, it finds it in more than one place:
XX1, XX2, XX3, XX4, XX5, XX6,XX7, XX8, XX9, XX10, XX11, XX12, XX13, XX14, XX15

with the likes of:
c.Value = "X1X, X2X, X3X, X4X, X5X, X6X, X7X, X8X, X9X, X10X, X11X, X12X, X13X, X14X, X15X"
and:
arrWords = Array("X1X", "X2X", "X3X", "X4X", "X5X", "X6X", "X7X", "X8X", "X9X", "X10X", "X11X", "X12X", "X13X", "X14X", "X15X")

but even that might trip up occasionally if the cells in columns D to S contain similar strings which might be found again on subsequent loops.
Use a character unlikely to be used such as ¬ or |.

which means use:
c.Value = "¬1¬, ¬2¬, ¬3¬, ¬4¬, ¬5¬, ¬6¬, ¬7¬, ¬8¬, ¬9¬, ¬10¬, ¬11¬, ¬12¬, ¬13¬, ¬14¬, ¬15¬"
and
arrWords = Array("¬1¬", "¬2¬", "¬3¬", "¬4¬", "¬5¬", "¬6¬", "¬7¬", "¬8¬", "¬9¬", "¬10¬", "¬11¬", "¬12¬", "¬13¬", "¬14¬", "¬15¬")

But this seems to be a very convoluted way of concatenating what's in the same row in columns D to S!

[dj44]

Hello my friend,

of course you make absolute and great sense!

My placeholders are too similar


ok let me fiddle first i will come back