Need to simplify code

A

alexwren

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")
 
L

Leo Heuser

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")
Click to expand...


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.
 
G

Guest

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

Ragdyer

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>
 
S

Sandy Mann

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

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
 
D

David F. Cox

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
 
A

alexwren

Sorry guys,

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

I shall go through these now.

Thanks in advance

Alex
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Case without Select Case error problem 2
external links 2
error trying to sort data 4
Conversion of excel vba code to access vba 3
Im confused! Can anyone "un"-confuse me? 1
Nested Do...Loops and Date Cell Defined as Date in VBA code 4
Help adjusting code to examine whole account groups first 1
modifying code troubles 1

Top