Offset in an Array - Replacement

[dj44]

Good Evening,

now i made an array.

I put my offset values in an array but so far theres a type mismatch

    oSearch = Array("A", "B", "C", "D")
    
    oReplace = Array("i-5", "i-3", "i-9", "i-4")
    
    For i = LBound(oSearch) To UBound(oSearch)
    For Each oCell In ThisWorkbook.Worksheets("Test").Range("K1:K10").Cells
    oCell.Replace What:=oSearch(i), Replacement:=oCell.Offset(, oReplace(i)).Value                           

    Next oCell
    Next i
    End Sub

I cant seem to work out why?

[mana]

?

oSearch = Array("A", "B", "C", "D")
 
oReplace = Array(-5, -3, -9, -4)
 
For i = LBound(oSearch) To UBound(oSearch)
    For Each oCell In ThisWorkbook.Worksheets("Test").Range("K1:K10").Cells
        oCell.Replace What:=oSearch(i), Replacement:=oCell.Offset(, i + oReplace(i)).Value
    Next oCell
Next i

[dj44]

Hello M,

thank you for your help.

now that looks like a better idea than mine.

However the values dont seem to enter correctly from the replace

Results.jpg



Using the below as a test

   oSearch = Array("A", "B", "C", "D")

    oReplace = Array("-4", "-3", "-2", "-1")


    For i = LBound(oSearch) To UBound(oSearch)
    For Each oCell In ThisWorkbook.Worksheets("Test").Range("K1:K10").Cells
        oCell.Replace What:=oSearch(i), Replacement:=oCell.Offset(, i + oReplace(i)).Value
    Next oCell
    Next i

I did try to use i- as well but that didnt work

[mancubus]

"-4": string
-4: number

[dj44]

Hello Mancubus,

oh yes well spotted

now i used this

oSearch = Array("A", "B", "C", "D")

oReplace = Array(-5, -4, -3, -2)

on the same set as above

but the result is 5,3,1 ' D is missing

it should be 5,4,3,2

there is something quite not right but i cant put my finger on it

[mancubus]

why don't you upload your workbook with the desired output?

[dj44]

I have attached my workbook
thank you

Offset - Array.xlsm

[mancubus]

i dont understand what is input and what is output from the sample data.

[Paul_Hossler]

I think this is what you're looking for

The orange is starting, blue is result


Capture.JPG

Option Explicit

 Sub Search_Replace_Array()
     Dim oCell As Range
     Dim i As Long
     Dim vOffset As Variant, vSearch As Variant
    
    vSearch = Array("A", "B", "C", "D")
    vOffset = Array(-9, -4, -7, -6)
    
    For i = LBound(vSearch) To UBound(vSearch)
        For Each oCell In ThisWorkbook.Worksheets("Test").Range("K2:K5").Cells
            Call oCell.Replace(vSearch(i), oCell.Offset(, vOffset(i)).Value, xlPart)
        Next oCell
    Next i
    
    End Sub

[dj44]

Hello Paul and Mancubus,

I am terribly sorry, at my usual inablity to explain.

Well it was meant to be very simple - so i thought


it was meant to be 9 4 7 6 in each of the cells in Column K

becuase in the array I set it to be oReplace=Array(-9,-4,-7-6)

In each cell we have A B C D

replace with the array offset.

so it would be the same in each cell in column K

Each cell would be 9,4,7,6

For the test data i made the values all the same thats why the confusion I am sorry

[dj44]

Image attached
Result.jpg

[Paul_Hossler]

Is post #9 wrong?

[dj44]

Pardon me Paul,

its been a long week, and begone are my spectacles.

No you did it perfecto as usual thank you very much

I cannot explain why mine didnt work, becasuse i did it similar, amiss the vSearch variant
Well i get really anxious over these arrays all the time as you know

it started off well and then it didnt


and i did check all my other arrays for help but that was a soup


Thank you for your generous help and other folk too

Now all have a great weekend