Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
numeric value, vba to split out one digit per column
A previous thread did not address this part of my question.
I have a series of numbers that have to be put on an excel paper form I designed to match the original. It has a box for each digit of the respective dollar amounts. Consider the following three numbers, which are 25000.25, 6050.79, 379.27. Obviously they must line up on the appropriate cents columns first, so their placement will vary depending on the size of the number involved. Essentially, the numbers above would look like this when the routine finishes: 2 5 0 0 0 2 5 6 0 5 0 7 9 3 7 9 2 7 No decimals as you can see. The total which will be below does not need to calculate from these converted digits, as it will be derived separately, and placed on the total line. What should the loop look like that places these three items. Just assume for now, the those numbers above are in Ranges, we'll say Amta1, Amta2, and Amta3. I appreciate the help. Bruce |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
numeric value, vba to split out one digit per column
"Revenue" wrote in message
... A previous thread did not address this part of my question. I have a series of numbers that have to be put on an excel paper form I designed to match the original. It has a box for each digit of the respective dollar amounts. Consider the following three numbers, which are 25000.25, 6050.79, 379.27. Obviously they must line up on the appropriate cents columns first, so their placement will vary depending on the size of the number involved. Essentially, the numbers above would look like this when the routine finishes: 2 5 0 0 0 2 5 6 0 5 0 7 9 3 7 9 2 7 No decimals as you can see. The total which will be below does not need to calculate from these converted digits, as it will be derived separately, and placed on the total line. What should the loop look like that places these three items. Just assume for now, the those numbers above are in Ranges, we'll say Amta1, Amta2, and Amta3. I appreciate the help. Bruce A little impatient, are we? see your original thread -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
numeric value, vba to split out one digit per column
On Wed, 24 Aug 2011 08:31:32 -0700 (PDT), Revenue wrote:
A previous thread did not address this part of my question. I have a series of numbers that have to be put on an excel paper form I designed to match the original. It has a box for each digit of the respective dollar amounts. Consider the following three numbers, which are 25000.25, 6050.79, 379.27. Obviously they must line up on the appropriate cents columns first, so their placement will vary depending on the size of the number involved. Essentially, the numbers above would look like this when the routine finishes: 2 5 0 0 0 2 5 6 0 5 0 7 9 3 7 9 2 7 No decimals as you can see. The total which will be below does not need to calculate from these converted digits, as it will be derived separately, and placed on the total line. What should the loop look like that places these three items. Just assume for now, the those numbers above are in Ranges, we'll say Amta1, Amta2, and Amta3. I appreciate the help. Bruce Here's a macro that will split the numbers out into a right justified series of columns. There is no error checking, and non-numbers will be split into a left-justified series of columns, but if you want something different, specify. I'm not sure how you want to select the source (src) range, but that part is easily changed in the macro: =============================== Option Explicit Sub SplitDigits() Dim rg As Range Dim lMaxLength As Long, lLen As Long Dim src As Variant Dim res() As Variant Dim i As Long, j As Long Set rg = Application.InputBox("Select Range of Data", Type:=8) src = rg For i = 1 To UBound(src, 1) src(i, 1) = Replace(Format(src(i, 1), "0.00"), ".", "") lLen = Len(src(i, 1)) If lLen lMaxLength Then lMaxLength = lLen Next i ReDim res(1 To UBound(src, 1), 1 To lMaxLength) For i = 1 To UBound(src, 1) For j = 1 To lMaxLength res(i, j) = Mid(Format(src(i, 1), Space(lMaxLength - Len(src(i, 1))) & "0"), j, 1) Next j Next i rg.Offset(columnoffset:=1).Resize(columnsize:=lMax Length) = res End Sub ========================== |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
numeric value, vba to split out one digit per column
A previous thread did not address this part of my question.
Actually, your question has been answered in the other thread. That thread got kind of long, so instead of making you search for it, here are the two functions I posted there. This first addresses your specific question for a 9-column print out... 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 one, a generalized solution, which allows you to specify any size for the number of columns via the Size constant (the Const statement)... 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 You might find it interesting that the statement inside the For..Next block is only one line long... I used line continuation characters to split the code line over three physical lines because it is so long (and to stop the newsgroup parser from word-wrapping it at awkward locations). Rick Rothstein (MVP - Excel) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Split 5 digit number into 5 cells | Excel Discussion (Misc queries) | |||
Need to split a 17 digit number into 17 cells by paste function. | Excel Discussion (Misc queries) | |||
Wildcard for finding the first numeric digit in a cell? | Excel Worksheet Functions | |||
Four digit numbers need to be split up | Excel Programming | |||
Four digit numbers need to be split up | Excel Programming |