View Full Version : [SOLVED:] Offset in an Array - Replacement
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?
?
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
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
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:
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
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
Paul_Hossler
12-15-2017, 09:21 AM
Is post #9 wrong?
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:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.