Need to simplify code

alexwren · Aug 6, 2006

Hi forum,

I know this is wrong - but it worked fine until I the the limit of how
much code I can have in the whole routine (causing a show stopping
error).

Can anyone suggest what i might use instead of this rather lenghty
script for replacing non filename compatible characters with acceptable
equivelants.

Code:
--------------------
Worksheets("out").Cells(ia, 17) = Worksheets("data").Cells(ib, 30)
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(32), "_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(33), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(34), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(35), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(37), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(38), "_and_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(39), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(40), "_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(41), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(42), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(43), "_and_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(44), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(46), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(47), "-")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(58), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(59), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(92), "-")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(96), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(128), "C")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(129), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(130), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(131), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(132), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(133), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(134), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(135), "c")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(136), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(137), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(138), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(139), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(140), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(141), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(142), "A")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(143), "A")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(144), "E")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(145), "ae")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(146), "AE")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(147), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(148), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(149), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(150), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(151), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(152), "y")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(153), "O")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(154), "U")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(160), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(161), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(162), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(163), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(164), "n")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(165), "N")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(226), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(232), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(233), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia, 17), Chr(244), "o")

Leo Heuser · Aug 6, 2006

alexwren said:
Hi forum,

I know this is wrong - but it worked fine until I the the limit of how
much code I can have in the whole routine (causing a show stopping
error).

Can anyone suggest what i might use instead of this rather lenghty
script for replacing non filename compatible characters with acceptable
equivelants.

Code:
--------------------
Worksheets("out").Cells(ia, 17) =
Worksheets("data").Cells(ib, 30)
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(32), "_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(33), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(34), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(35), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(37), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(38), "_and_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(39), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(40), "_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(41), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(42), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(43), "_and_")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(44), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(46), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(47), "-")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(58), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(59), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(92), "-")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(96), "")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(128), "C")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(129), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(130), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(131), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(132), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(133), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(134), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(135), "c")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(136), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(137), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(138), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(139), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(140), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(141), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(142), "A")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(143), "A")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(144), "E")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(145), "ae")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(146), "AE")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(147), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(148), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(149), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(150), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(151), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(152), "y")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(153), "O")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(154), "U")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(160), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(161), "i")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(162), "o")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(163), "u")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(164), "n")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(165), "N")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(226), "a")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(232), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(233), "e")
Worksheets("out").Cells(ia, 17) = Replace(Worksheets("out").Cells(ia,
17), Chr(244), "o")

Hi Alex

Try something along these lines (untested)

Sub Test()
Dim Counter As Long
Dim ReplaceChar As Variant

ReplaceChar = Array(Array(32, "_"), Array(33, ""), Array(34, ""), _
Array(35, ""), Array(37, ""), _
Array(38, "_and_"), Array(39, ""), Array(40, "_"))

Worksheets("out").Cells(ia, 17) = Worksheets("data").Cells(ib, 30)

For Counter = LBound(ReplaceChar) To UBound(ReplaceChar)
Worksheets("out").Cells(ia, 17) = _
Replace(Worksheets("out").Cells(ia, 17), ReplaceChar(Counter,
1), _
ReplaceChar(Counter, 2))
Next Counter

End Sub

Make the lines in ReplaceChar as long as possible before making a
line shift with SpaceUnderlineEnter.
Also it may be necessary to make ReplaceChar1, ReplaceChar2 etc and run
several loops in succession to get all characters replaced.

Guest · Aug 6, 2006

Create two arrays of integers to hold the CHR indices. Then use a simple
loop to perform the translations

Ragdyer · Aug 6, 2006

AHhhh ! ... how easy to read Gary's top post, compared to reading Leo's
bottom post.

Some day the light will dawn in the minds of all bottom posters.<bg>

Sandy Mann · Aug 6, 2006

Ragdyer said:
AHhhh ! ... how easy to read Gary's top post, compared to reading Leo's
bottom post.

Ah yes, but which end of a boiled egg do you open? <g>

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

Jim Cone · Aug 6, 2006

Amen
(however, Leo could post sideways and he would still be welcome in my opinion)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

AHhhh ! ... how easy to read Gary's top post, compared to reading Leo's
bottom post.
Some day the light will dawn in the minds of all bottom posters.<bg>

David F. Cox · Aug 6, 2006

EOA - I have been through too many versions of basic to get syntax right

something like:-
outstr =""
for I = 1 to len(source)
outchar =""
j = asc(mid(source,I,1))
select case j
case 38,43
outchar = "_and_"
case 131 to 134
outchar = "a"

........
case else
outchar = chr(j)
end select
next
outstr = outstr & outchar

alexwren · Aug 15, 2006

Sorry guys,

I did not get any emails to say that anyone had replied...

I shall go through these now.

Thanks in advance

Alex

Need to simplify code

alexwren

Leo Heuser

Guest

Ragdyer

Sandy Mann

Jim Cone

David F. Cox

alexwren