Need to simplify code

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.

Please Login or Register  to view this content.

"alexwren" <[email protected]> skrev i
en meddelelse news:[email protected]...
>
> 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")
> --------------------
>
>
> --
> alexwren


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.



--
Best regards
Leo Heuser

Followup to newsgroup only please.

Create two arrays of integers to hold the CHR indices. Then use a simple
loop to perform the translations
--
Gary's Student


"alexwren" wrote:

>
> 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")
> --------------------
>
>
> --
> alexwren
> ------------------------------------------------------------------------
> alexwren's Profile: http://www.excelforum.com/member.php...o&userid=36767
> View this thread: http://www.excelforum.com/showthread...hreadid=568724
>
>

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>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> Create two arrays of integers to hold the CHR indices. Then use a simple
> loop to perform the translations
> --
> Gary's Student
>
>
> "alexwren" wrote:
>
> >
> > 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")
> > --------------------
> >
> >
> > --
> > alexwren
> > ------------------------------------------------------------------------
> > alexwren's Profile:
http://www.excelforum.com/member.php...o&userid=36767
> > View this thread:
http://www.excelforum.com/showthread...hreadid=568724
> >
> >

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



"Ragdyer" <[email protected]> wrote in message news:%[email protected]...
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>
--
Regards,

RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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")
> --------------------
>
>
> --
> alexwren
> ------------------------------------------------------------------------
> alexwren's Profile:
> http://www.excelforum.com/member.php...o&userid=36767
> View this thread: http://www.excelforum.com/showthread...hreadid=568724
>

"Ragdyer" <[email protected]> wrote in message
news:%[email protected]...
> 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

[email protected]
[email protected] with @tiscali.co.uk

Sorry guys,

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

I shall go through these now.

Thanks in advance

Alex