Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Need to simplify code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 266
Default 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.


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11,058
Default 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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,572
Default 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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 3,290
Default 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 !
---------------------------------------------------------------------------

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,345
Default 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




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 11
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
code not unique find latest date Barbara Wiseman Excel Discussion (Misc queries) 3 December 11th 05 08:50 AM
VLOOKUP for Zip Code Ranges JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM
Conform a total to a list of results? xmaveric Excel Discussion (Misc queries) 1 August 21st 05 07:22 PM
Macro for changing text to Proper Case JPriest Excel Worksheet Functions 3 August 8th 05 09:31 PM
close form code tkaplan Excel Discussion (Misc queries) 1 June 3rd 05 10:49 PM


All times are GMT +1. The time now is 11:50 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement