PDA

View Full Version : Search Replace Offset - substitute



dj44
10-27-2017, 07:13 AM
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
10-27-2017, 08:49 AM
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
10-27-2017, 10:14 AM
Hello my friend,

of course you make absolute and great sense!

My placeholders are too similar:doh:


ok let me fiddle first i will come back