Clif McIrvin laid this down on his screen :
"Rick Rothstein" wrote in message
...
Why so many lines of code? <g

Sub ParseAmountsRick()
Dim Cell As Range
For Each Cell In Selection
Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), "@_@_@_@_@_@_@_@_@"), "_")
Next
End Sub

If anyone is interested, here is my code generalized to allow the user to
set number of cells to fill via a Size constant (the Const statement)...

Sub ParseAmountsRick()
Dim Cell As Range
Const Size As Long = 9
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), Left(Replace(String(Size, _
"@"), "@", "@_"), 2 * Size - 1)), "_")
Next
End Sub

Rick Rothstein (MVP - Excel)


To generalize further, use a defined name instead of a Const:

For instance, create a Defined Name such as: Defined_Name=9
(this works in xl2010, I don't know how far back you can assign values to
defined names.)

Sub ParseAmountsRick()
Dim Cell As Range
Dim Size As Long
Size = [Defined_Name]
For Each Cell In Selection
Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _
Cell.Text, ".", ""), ",", ""), Left(Replace(String(Size, _
"@"), "@", "@_"), 2 * Size - 1)), "_")
Next
End Sub

I can verify that storing a value in a defined name works as far back
as xl2000. Whether it works further back I can't say because I've never
developed for earlier versions. *However*, it would be harder to update
than changing a constant at runtime, *AND* the defined name isn't as
portable as is the code!<g

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc