I was going to work this out with excel formulas like text, mid, etc,
but this is not going to be a simple approach even going that way.

Ok, here are two separate form problems I have.

Form 1 requires whole dollars in one column, and the cents in
another. So, basically I have to drop the correct total in the right
bucket if we are going to do it through programming.

ie 27,651.09 needs to be "27,651" in one column and "09" in the
one
right next to it. These numbers are formula driven totals calculated
elsewhere that must be transformed for this one form, as archaic as
the form happens to be.

Form 2 is worse, this would take a number like "27,651.09" and divide
it one digit at a time for the approximate box on the printed form.


ie 2|7|6|5|1|0|9|

I need a straightforward approach as possible, because I usually get
criticized when I get too fancy.

Ideas greatly appreciated.


Bruce

Try...

Sub ParseAmount()
Dim vTemp As Variant
vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As Variant
RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

--
Garry

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

GS presented the following explanation :
Try...

Sub ParseAmount()
Dim vTemp As Variant
vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As Variant
RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

Assumes ColA is where the amounts are listed, ColB and ColC is where
the parsed values go.

Assumes you want a 2-digit result for the cents in ColC, AND you will
format ColB to include thousands separator. Otherwise, the function is
not needed...

Sub ParseAmount2()
Dim vTemp As Variant
vTemp = Split(Range("A1").Value, ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

OR...
If you want the result to be formatted as text (ie: left aligned):


Sub ParseAmount3()
With Range("A1")
.Offset(, 1).Resize(1, 2) = Split(.Value, ".")
End With 'Range("A1")
End Sub

--
Garry

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

On Aug 23, 4:00*pm, GS wrote:
GS presented the following explanation :





Try...

Sub ParseAmount()
* Dim vTemp As Variant
* vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
* With Range("B1")
* * .Value = vTemp(0)
* * With .Offset(, 1)
* * * .NumberFormat = "00": .Value = vTemp(1)
* * End With '.Offset(, 1)
* End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As Variant
* RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

Assumes ColA is where the amounts are listed, ColB and ColC is where
the parsed values go.

Assumes you want a 2-digit result for the cents in ColC, AND you will
format ColB to include thousands separator. Otherwise, the function is
not needed...

Sub ParseAmount2()
* Dim vTemp As Variant
* vTemp = Split(Range("A1").Value, ".")
* With Range("B1")
* * .Value = vTemp(0)
* * With .Offset(, 1)
* * * .NumberFormat = "00": .Value = vTemp(1)
* * End With '.Offset(, 1)
* End With 'Range("B1")
End Sub

OR...
If you want the result to be formatted as text (ie: left aligned):

Sub ParseAmount3()
* With Range("A1")
* * .Offset(, 1).Resize(1, 2) = Split(.Value, ".")
* End With 'Range("A1")
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

Ok, I liked the approach above best for spreading the number between
two columns.

Now, how do I get the 2nd part of my puzzle to work where 27651.09
becomes split one digit to a column?

Somehow when I get this looping and putting numbers in different
places, the routine needs to start in the right column. For example,
if the next row has 107150.25, then the 1 in this example must start
in the appropriate column so that the placeholders line up properly .
It would be best for it to count from the right most column; I am
allowed 9 columns in the table. These digits all represent dollars and
cents.

1 0 7 1 5 0 2 5
2 7 6 5 1 0 9
7 0 2 5 1 1

"Revenue" wrote in message
...
On Aug 23, 4:00 pm, GS wrote:
GS presented the following explanation :





Try...

Sub ParseAmount()
Dim vTemp As Variant
vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As
Variant
RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

Assumes ColA is where the amounts are listed, ColB and ColC is where
the parsed values go.

Assumes you want a 2-digit result for the cents in ColC, AND you will
format ColB to include thousands separator. Otherwise, the function is
not needed...

Sub ParseAmount2()
Dim vTemp As Variant
vTemp = Split(Range("A1").Value, ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

OR...
If you want the result to be formatted as text (ie: left aligned):

Sub ParseAmount3()
With Range("A1")
.Offset(, 1).Resize(1, 2) = Split(.Value, ".")
End With 'Range("A1")
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted
text -

- Show quoted text -

Ok, I liked the approach above best for spreading the number between
two columns.

Now, how do I get the 2nd part of my puzzle to work where 27651.09
becomes split one digit to a column?

Somehow when I get this looping and putting numbers in different
places, the routine needs to start in the right column. For example,
if the next row has 107150.25, then the 1 in this example must start
in the appropriate column so that the placeholders line up properly .
It would be best for it to count from the right most column; I am
allowed 9 columns in the table. These digits all represent dollars and
cents.

1 0 7 1 5 0 2 5
2 7 6 5 1 0 9
7 0 2 5 1 1


---------

Here's a bit of air code just showing one way to grab the individual
digits starting from the right. I leave it up to you haow to use it.

I use the format function to guarantee there will always be two decimal
places; if there happen to be more than two in the source data format
will round the result.

I'm using A1 as the source cell.

dim strData as string
dim strDigit as string
dm ii as integer

strData=format([A1],"0.00")

for ii = len(strData) to 1 step -1
strDigit=mid(strData,ii,1)
if strDigit < "." then ' ignore decimal point ... remove if ...
endif to include "."
your code here
endif
next ii


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Clif McIrvin wrote on 8/24/2011 :
dim strData as string
dim strDigit as string
dm ii as integer

strData=format([A1],"0.00")

for ii = len(strData) to 1 step -1
strDigit=mid(strData,ii,1)
if strDigit < "." then ' ignore decimal point ... remove if ... endif to
include "."
your code here
endif
next ii

Clif,
You could eliminate the check for the decimal by stripping it out
before you loop...

strData = Replace(Format([A1], "0.00"), ".", "")

--
I was thinking to 'pad' the string to always be Len=9. The wks can be
CF'd to hide leading zeros in the first 7 cols. This way, the digits
can be loaded into an array and 'dumped' into the wks.

--
Garry

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

"Revenue" wrote in message
...
On Aug 23, 4:00 pm, GS wrote:
GS presented the following explanation :





Try...

Sub ParseAmount()
Dim vTemp As Variant
vTemp = Split(RemoveCharacters(Range("A1").Value, ","), ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

Function RemoveCharacters(Amount As Double, Char As String) As
Variant
RemoveCharacters = Replace(CStr(Amount), Char, "")
End Function

Assumes ColA is where the amounts are listed, ColB and ColC is where
the parsed values go.

Assumes you want a 2-digit result for the cents in ColC, AND you will
format ColB to include thousands separator. Otherwise, the function is
not needed...

Sub ParseAmount2()
Dim vTemp As Variant
vTemp = Split(Range("A1").Value, ".")
With Range("B1")
.Value = vTemp(0)
With .Offset(, 1)
.NumberFormat = "00": .Value = vTemp(1)
End With '.Offset(, 1)
End With 'Range("B1")
End Sub

OR...
If you want the result to be formatted as text (ie: left aligned):

Sub ParseAmount3()
With Range("A1")
.Offset(, 1).Resize(1, 2) = Split(.Value, ".")
End With 'Range("A1")
End Sub

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted
text -

- Show quoted text -

Ok, I liked the approach above best for spreading the number between
two columns.

Now, how do I get the 2nd part of my puzzle to work where 27651.09
becomes split one digit to a column?

Somehow when I get this looping and putting numbers in different
places, the routine needs to start in the right column. For example,
if the next row has 107150.25, then the 1 in this example must start
in the appropriate column so that the placeholders line up properly .
It would be best for it to count from the right most column; I am
allowed 9 columns in the table. These digits all represent dollars and
cents.

1 0 7 1 5 0 2 5
2 7 6 5 1 0 9
7 0 2 5 1 1



-----------

another approach, using worksheet functions:

=MID(TEXT(A1,"0000000.00"),1,1)
=MID(TEXT(A1,"0000000.00"),2,1)
=MID(TEXT(A1,"0000000.00"),3,1)
=MID(TEXT(A1,"0000000.00"),4,1)
=MID(TEXT(A1,"0000000.00"),5,1)
=MID(TEXT(A1,"0000000.00"),6,1)
=MID(TEXT(A1,"0000000.00"),7,1)
=MID(TEXT(A1,"0000000.00"),9,1)
=MID(TEXT(A1,"0000000.00"),10,1)


--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)

Revenue formulated on Wednesday :
Now, how do I get the 2nd part of my puzzle to work where 27651.09
becomes split one digit to a column?

Somehow when I get this looping and putting numbers in different
places, the routine needs to start in the right column. For example,
if the next row has 107150.25, then the 1 in this example must start
in the appropriate column so that the placeholders line up properly .
It would be best for it to count from the right most column; I am
allowed 9 columns in the table. These digits all represent dollars and
cents.

1 0 7 1 5 0 2 5
2 7 6 5 1 0 9
7 0 2 5 1 1

Here's one way to handle the 2nd part...

Assume amounts are in ColA, and listed contiguously (ie: NO blanks).

Select the amounts to be parsed and then run this macro:

Sub ParseAmount4()
Dim sTemp As String, sVal As String
Dim c As Variant, i As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
sVal = "" '//initialize
For i = 1 To Len(sTemp)
sVal = sVal & "," & Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, Len(sTemp)) = Split(Mid$(sVal, 2), ",")
Next 'c
End Sub

--
Garry

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

I forgot to mention that you should substitute the column offset for
the 1st column of the location for the output. That means...

If starting at ColE then the offset would be:
Columns("E").Column - Columns("A").Column

Also, as I mentioned to Clif, you can use CF to hide the leading zeros
for any/all of the 1st 7 digits.

--
Garry

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

Here's the array approach I mentioned. It does not enter leading zeros.

(Same assumptions apply as for previous post)

Sub ParseAmounts5()
Dim sTemp As String, vTemp(1 To 9) As Variant
Dim c As Variant, i As Integer, iLen As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
iLen = 1 '//initialize counter
For i = 1 To Len(sTemp)
If Mid$(sTemp, i, 1) 0 Then Exit For
iLen = iLen + 1
Next 'i
For i = iLen To UBound(vTemp)
vTemp(i) = Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, UBound(vTemp)) = vTemp
Erase vTemp
Next 'c
End Sub

--
Garry

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

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

Rick Rothstein (MVP - Excel)


Here 's the array approach I mentioned. It does not enter leading zeros.

(Same assumptions apply as for previous post)

Sub ParseAmounts5()
Dim sTemp As String, vTemp(1 To 9) As Variant
Dim c As Variant, i As Integer, iLen As Integer
For Each c In Selection
sTemp = Replace(Format(c.Value, "0000000.00"), ".", "")
iLen = 1 '//initialize counter
For i = 1 To Len(sTemp)
If Mid$(sTemp, i, 1) 0 Then Exit For
iLen = iLen + 1
Next 'i
For i = iLen To UBound(vTemp)
vTemp(i) = Mid$(sTemp, i, 1)
Next 'i
c.Offset(, 1).Resize(1, UBound(vTemp)) = vTemp
Erase vTemp
Next 'c
End Sub