Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
"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 :-) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
"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 :-) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
"GS" wrote in message
... 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. Good use of replace. When always padding to 9 digits ocurred to me I went with worksheet formulas instead of VBA. Using your array idea, you could still work right to left and leave leading zeroes as empty variants; i.e., BLANK cells and CF wouldn't be necessary (and skip the padding.) -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Clif McIrvin formulated the question :
"GS" wrote in message ... 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. Good use of replace. When always padding to 9 digits ocurred to me I went with worksheet formulas instead of VBA. Using your array idea, you could still work right to left and leave leading zeroes as empty variants; i.e., BLANK cells and CF wouldn't be necessary (and skip the padding.) Clif, In my reply code, I decided not to use an array since it was simpler to 'dump' one row at a time into the corresponding results cells. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
I was wondering if you were going to weigh in ... I like it!
"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 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 -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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) |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Rick Rothstein expressed precisely :
Why so many lines of code? <g Just for clarity and ease for the OP to understand (better self-documentation), AND because this solution uses an array. 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 I also wondered when/if you'd join in! Very nice. It's what I'd prefer over using the array approach. However, using Cell.Text doesn't work when the cells are formatted 'General' and the amounts are pasted in. *Typing* 107150.25 in A1 displays as 107150.3, indicating Excel does some 'unsolicited' rounding. Typing 2765.11 and 7025.11 displays as typed. Programmatic entry displays to precision (ie: without rounding). Also, the thousands separator would not be present if the value was not text to begin with. I guess it can go either way depending on how the values were captured, but all 3 functions convert numeric values to text anyway. So typing 107,150.25 in A1 displays as typed (no rounding occurs) BUT the Formula Bar does not contain the comma. In this case, Excel formats the cell to the display thousands separator but stores the value without it. -- Since we format the value, I'd use Cell.Value... Sub ParseAmountsRick() Dim Cell As Range For Each Cell In Selection Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace( _ Cell.Value, ".", ""), "@_@_@_@_@_@_@_@_@"), "_") Next End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
"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 -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Rick Rothstein submitted this idea :
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)... Good idea! 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) I'm not sure I'd go with the extra processing when the number of amounts could be in the thousands! Again... Sub ParseAmountsRick() Dim Cell As Range Const Size As Long = 9 For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace( _ Cell.Value, ".", ""), "@_@_@_@_@_@_@_@_@"), "_") Next End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
"GS" wrote in message
... *However*, it would be harder to update than changing a constant at runtime, You just lost me .... changing a constant at runtime??? Don't you mean compile time? My thinking was with a worksheet defined value, there would be no need to open the VBE to change the parameter. *AND* the defined name isn't as portable as is the code!<g Very true. The choice of solution depends greatly on developer preference and the specific characteristics of the work environment, doesn't it?! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Clif McIrvin used his keyboard to write :
"GS" wrote in message ... *However*, it would be harder to update than changing a constant at runtime, You just lost me .... changing a constant at runtime??? Don't you mean compile time? I meant editing the value when you want to use the code for a new number of digits. Ergoeditrun! My thinking was with a worksheet defined value, there would be no need to open the VBE to change the parameter. If you mean use a named range on the wks then that's easier than editing a defined name value, but still not portable with the code. *AND* the defined name isn't as portable as is the code!<g Very true. The choice of solution depends greatly on developer preference and the specific characteristics of the work environment, doesn't it?! Basically! I tend to make reusable code as friendly as possible, though, and so I'd likely prompt myself for the number of digits to format to. In this case I'd use Rick's idea of implementing the String() function for constructing the format string.<g -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Great comments everyone.... Thanks a lot...
I will have to study each of these carefully although I temporarily at least worked out an approach with formulas like =mid(range1,range2,1), and then going right to left, I subtracted 1 from the number in range2 to make it pick the previous digit. Range2 just contained a len command of the text cell in range 1. |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
It happens that GS formulated :
Clif McIrvin used his keyboard to write : "GS" wrote in message ... *However*, it would be harder to update than changing a constant at runtime, You just lost me .... changing a constant at runtime??? Don't you mean compile time? I meant editing the value when you want to use the code for a new number of digits. Ergoeditrun! My thinking was with a worksheet defined value, there would be no need to open the VBE to change the parameter. If you mean use a named range on the wks then that's easier than editing a defined name value, but still not portable with the code. *AND* the defined name isn't as portable as is the code!<g Very true. The choice of solution depends greatly on developer preference and the specific characteristics of the work environment, doesn't it?! Basically! I tend to make reusable code as friendly as possible, though, and so I'd likely prompt myself for the number of digits to format to. In this case I'd use Rick's idea of implementing the String() function for constructing the format string.<g Example... Sub ParseAmountsRick3() Dim Cell As Range, sFormat As String, Size As Long Size = Application.InputBox("Enter the number of digits", Type:=1) sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2) For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace( _ Cell.Value, ".", ""), sFormat), "_") Next End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
GS has brought this to us :
Rick Rothstein expressed precisely : Why so many lines of code? <g Just for clarity and ease for the OP to understand (better self-documentation), AND because this solution uses an array. 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 I also wondered when/if you'd join in! Very nice. It's what I'd prefer over using the array approach. However, using Cell.Text doesn't work when the cells are formatted 'General' and the amounts are pasted in. *Typing* 107150.25 in A1 displays as 107150.3, indicating Excel does some 'unsolicited' rounding. Typing 2765.11 and 7025.11 displays as typed. Programmatic entry displays to precision (ie: without rounding). Also, the thousands separator would not be present if the value was not text to begin with. I guess it can go either way depending on how the values were captured, but all 3 functions convert numeric values to text anyway. So typing 107,150.25 in A1 displays as typed (no rounding occurs) BUT the Formula Bar does not contain the comma. In this case, Excel formats the cell to the display thousands separator but stores the value without it. -- Since we format the value, I'd use Cell.Value... Sub ParseAmountsRick() Dim Cell As Range For Each Cell In Selection Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace( _ Cell.Value, ".", ""), "@_@_@_@_@_@_@_@_@"), "_") Next End Sub Well.., all might NOT be as I stated. Seems the rounding disappeared when the column width was AutoFit. Kind of makes the rounding issue mute unless the data is dumped into fixed width cols. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Amended example:
If user cancels InputBox... Sub ParseAmountsRick3() Dim Cell As Range, sFormat As String, Size As Long Size = Application.InputBox("Enter the number of digits", Type:=1) If Not Size = False Then sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2) For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = _ Split(Format(Replace( Cell.Value, ".", ""), sFormat), "_") Next End If 'Not Size = False End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
Why so many lines of code? <g
Just for clarity and ease for the OP to understand (better self-documentation), AND because this solution uses an array. I'm guessing you read right over the <g symbol, right? 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 However, using Cell.Text doesn't work when the cells are formatted 'General' and the amounts are pasted in. *Typing* 107150.25 in A1 displays as 107150.3, indicating Excel does some 'unsolicited' rounding. Typing 2765.11 and 7025.11 displays as typed. Programmatic entry displays to precision (ie: without rounding). Also, the thousands separator would not be present if the value was not text to begin with. I guess it can go either way depending on how the values were captured, but all 3 functions convert numeric values to text anyway. So typing 107,150.25 in A1 displays as typed (no rounding occurs) BUT the Formula Bar does not contain the comma. In this case, Excel formats the cell to the display thousands separator but stores the value without it. The reason I went with the Text property was that I was trying to cater to the selected value being formatted as Text or as Numbers with 2-decimal places. The problem with using the Value property with numbers is that loss of trailing zeroes in numbers like 12345.00 or 12345.10. Anyway, I went back to the drawing board and came up with this for the specific 9-cell version... Sub ParseAmountsNewRick() Dim Cell As Range For Each Cell In Selection Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace(Replace( _ Format$(Cell.Value, "0.00"), ".", ""), ",", ""), _ "@_@_@_@_@_@_@_@_@"), "_") Next End Sub and this for the generalized solution... Sub ParseAmountsNewRickToo() Dim Cell As Range Const Size As Long = 11 For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace(Replace( _ Format$(Cell.Value, "0.00"), ".", ""), ",", ""), _ Mid(Replace(String(Size, "@"), "@", "_@"), 2)), "_") Next End Sub both version of which will handle the selected numbers being formatted as Text or as Number with two decimal places. Rick Rothstein (MVP - Excel) |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
It happens that Rick Rothstein formulated :
Why so many lines of code? <g Just for clarity and ease for the OP to understand (better self-documentation), AND because this solution uses an array. I'm guessing you read right over the <g symbol, right? No! I took it as meant! I actually think I wrote more code than need be, but didn't have time to trim it down AND wasn't looking to make it harder to understand not knowing the OP's level of skill!<bg<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 However, using Cell.Text doesn't work when the cells are formatted 'General' and the amounts are pasted in. *Typing* 107150.25 in A1 displays as 107150.3, indicating Excel does some 'unsolicited' rounding. Typing 2765.11 and 7025.11 displays as typed. Programmatic entry displays to precision (ie: without rounding). Also, the thousands separator would not be present if the value was not text to begin with. I guess it can go either way depending on how the values were captured, but all 3 functions convert numeric values to text anyway. So typing 107,150.25 in A1 displays as typed (no rounding occurs) BUT the Formula Bar does not contain the comma. In this case, Excel formats the cell to the display thousands separator but stores the value without it. The reason I went with the Text property was that I was trying to cater to the selected value being formatted as Text or as Numbers with 2-decimal places. The problem with using the Value property with numbers is that loss of trailing zeroes in numbers like 12345.00 or 12345.10. And so is why Clif suggested formatting the 'Value' to 2 decimal places. I later think I should have stuck with that: ..Format(c.Value, "0.00")... ...because if the value was "12,345" (text) then Format("12,345","0.00" results to 12345.00. If the value was 12,345 (numeric) then Format(12,345,"0.00" results to 12345.00. So.., text or numeric it still adds the decimal precision regardless if the value is a whole number OR text, -AND- it removes any commas, no? Anyway, I went back to the drawing board and came up with this for the specific 9-cell version... Sub ParseAmountsNewRick() Dim Cell As Range For Each Cell In Selection Cell.Offset(, 1).Resize(, 9) = Split(Format(Replace( _ Format$(Cell.Value, "0.00"), ".", ""), _ "@_@_@_@_@_@_@_@_@"), "_") Next End Sub and this for the generalized solution... Sub ParseAmountsNewRickToo() Dim Cell As Range Const Size As Long = 11 For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = Split(Format(Replace( _ Format$(Cell.Value, "0.00"), ".", ""), _ Mid(Replace(String(Size, "@"), "@", "_@"), 2)), "_") Next End Sub Why Size=11? (000,000,000.00)? both version of which will handle the selected numbers being formatted as Text or as Number with two decimal places. Agreed! However, I don't think we need to include the Replace for the thousands separator. I tried this in the immediate Window and also on the wks, and the Format function (as used) removes the comma[s]. Value as text: Format("12345", "0.00") returns 12345.00 Format("12,345", "0.00") returns 12345.00 Format("12345.00", "0.00") returns 12345.00 Format("12345.10", "0.00") returns 12345.10 Same results for Value as numbers! Rick Rothstein (MVP - Excel) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Spliting digits up to fit paper form
GS expressed precisely :
Amended example: If user cancels InputBox... Sub ParseAmountsRick3() Dim Cell As Range, sFormat As String, Size As Long Size = Application.InputBox("Enter the number of digits", Type:=1) If Not Size = False Then sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2) For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = _ Split(Format(Replace( Cell.Value, ".", ""), sFormat), "_") Next End If 'Not Size = False End Sub Revised as per discussion with Rick to retain Clif's idea to format the cell value to 2 decimal places so it works with text or numbers... Sub ParseAmountsRick3() Dim Cell As Range, sFormat As String, Size As Long Size = Application.InputBox("Enter the number of digits", Type:=1) If Not Size = False Then sFormat = Mid$(Replace(String(Size, "@"), "@", "_@"), 2) For Each Cell In Selection Cell.Offset(, 1).Resize(, Size) = _ Split(Format(Replace(Format(Cell.Value, "0.00"), ".", ""), sFormat), "_") Next End If 'Not Size = False End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you change single digits to recognized double digits? | Excel Worksheet Functions | |||
divide numbers up for a paper form | Excel Worksheet Functions | |||
VBA write macro change column with 3 number digits to 4 digits the | Excel Discussion (Misc queries) | |||
Mattter typed on Legal paper want to get it on A4 size paper | Excel Programming | |||
Can I scan an existing paper form into excel | Excel Discussion (Misc queries) |