ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Need to simplify code (https://www.excelbanter.com/excel-discussion-misc-queries/103395-need-simplify-code.html)

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


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

Leo Heuser
Need to simplify code
 
"alexwren" skrev i
en meddelelse ...

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.

Gary''s Student
Need to simplify code
 
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

RagDyeR
Need to simplify code
 
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" wrote in message
...
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

Sandy Mann
Need to simplify code
 
"Ragdyer" wrote in message
...
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


 with @tiscali.co.uk

Jim Cone
Need to simplify code
 
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" wrote in message ...
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 !
---------------------------------------------------------------------------

David F. Cox
Need to simplify code
 
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" wrote in
message ...

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

alexwren
Need to simplify code
 

Sorry guys,

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

I shall go through these now.

Thanks in advance

Alex


--
alexwren
------------------------------------------------------------------------
alexwren's Profile: http://www.excelforum.com/member.php...o&userid=36767
View this thread: http://www.excelforum.com/showthread...hreadid=568724


All times are GMT +1. The time now is 02:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com