PDA

View Full Version : [SOLVED] Offset in an Array - Replacement



dj44
12-14-2017, 03:33 PM
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
12-15-2017, 02:57 AM
?


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
12-15-2017, 04:08 AM
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

21183



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
12-15-2017, 04:46 AM
"-4": string
-4: number

dj44
12-15-2017, 05:32 AM
Hello Mancubus,

oh yes well spotted :doh:

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
12-15-2017, 05:34 AM
why don't you upload your workbook with the desired output?
:hi:

dj44
12-15-2017, 06:04 AM
I have attached my workbook
thank you

21184

mancubus
12-15-2017, 07:56 AM
i dont understand what is input and what is output from the sample data.

Paul_Hossler
12-15-2017, 08:00 AM
I think this is what you're looking for

The orange is starting, blue is result


21187




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
12-15-2017, 08:17 AM
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
12-15-2017, 08:23 AM
Image attached
21188

Paul_Hossler
12-15-2017, 09:21 AM
Is post #9 wrong?

dj44
12-15-2017, 09:31 AM
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

:beerchug: