Hello everyone!
I`m trying to use a Split for find and replace invalid symbols from my data, as there are A LOT of symbols, doing it line by line seems not correct. I like to use Split for these types of things, but this time it is not working as Split adds "" to the values...
This would be and example of what i`m using:
BAD = Split("ChrW(&Hc0)|ChrW(&Hc1)|ChrW(&Hc2)|ChrW(&Hc3)|ChrW(&Hc4)|ChrW(&Hc5)|ChrW(&H100)", "|")
GOOD = Split("A|A|A|A|A|A|A", "|")
For k = 0 To UBound(BAD)
m = k
Selection.Replace What:=BAD(k), Replacement:=GOOD(m), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next k
It doesn't work as the line after BAD GOOD is added looks like this:
Selection.Replace What:="ChrW(&Hc0)", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
And it needs to look like this to work:
Selection.Replace What:=ChrW(&Hc0), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
The example i gave is quite small, in my data i have about 150 minimum character codes, so adding line by line is not gonna work..
Any ideas how I could make it work? How not to add "" for example?
Any ideas would really help! Thank you!!
I`m trying to use a Split for find and replace invalid symbols from my data, as there are A LOT of symbols, doing it line by line seems not correct. I like to use Split for these types of things, but this time it is not working as Split adds "" to the values...
This would be and example of what i`m using:
BAD = Split("ChrW(&Hc0)|ChrW(&Hc1)|ChrW(&Hc2)|ChrW(&Hc3)|ChrW(&Hc4)|ChrW(&Hc5)|ChrW(&H100)", "|")
GOOD = Split("A|A|A|A|A|A|A", "|")
For k = 0 To UBound(BAD)
m = k
Selection.Replace What:=BAD(k), Replacement:=GOOD(m), LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Next k
It doesn't work as the line after BAD GOOD is added looks like this:
Selection.Replace What:="ChrW(&Hc0)", Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
And it needs to look like this to work:
Selection.Replace What:=ChrW(&Hc0), Replacement:="A", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
The example i gave is quite small, in my data i have about 150 minimum character codes, so adding line by line is not gonna work..
Any ideas how I could make it work? How not to add "" for example?
Any ideas would really help! Thank you!!