Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bob Phillips - Please
Bob,
On the 13th Janaury you were kind enough to provide me with a function that concatenated values across approx 70 columns for a dynamic number of rows. That code is posted below. Since then l have been testing the workbook, before releasing it, and have now found a problem. The user can enter a 3 digit code in any of the 70 columns up to a maximum of 15. Therefore the maximum number of digits that can be concatenated is 45. However when that number of digits is concatenated the result ends up as a number raised to a power.(The function works fine when only 4 or 5 sets of 3 digits are entered) ie these digits when concatenated 100 101 200 201 203 219 225 300 301 400 401 500 550 600 900 end up being displayed as 100101200201203000000000000000000000000000000 and the value displayed in the formula bar is 1.00101200201203E+44 what l really need to see is a string as follows 100101200201203219225300301400401500550600 because in the next 15 columns l extract each of the 3 digit codes using functions (for example Left(A1,3) Mid(A1,4,3) etc) Any ideas as to whats going wrong and what the solution is please? Alternatively is it possible to 'loop thru' each of the 70 columns and when the 1st 3 digit value is found put it into say column 100, the 2nd 3 digit value in column 101 etc for each row in the dynamic range? xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx This works from row 1 to the last row and concatenates values from column 2 out, and drop the result in column A. Change to suit Sub ConcatenateRange() Dim val As String Dim iLastRow As Long Dim iLastCol As Long Dim i As Long Dim j As Long iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column val = "" For j = 2 To iLastCol val = val & Cells(i, j) Next j Cells(i, "A").Value = val Next i End Sub Kind regards Michael Beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bob Phillips - Please
Michael,
Try this Sub ConcatenateRange() Dim val As String Dim iLastRow As Long Dim iLastCol As Long Dim i As Long Dim j As Long Columns(1).NumberFormat = "@" iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column val = "" For j = 2 To iLastCol val = val & Cells(i, j) Next j Cells(i, "A").Value = val Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Michael Beckinsale" wrote in message ... Bob, On the 13th Janaury you were kind enough to provide me with a function that concatenated values across approx 70 columns for a dynamic number of rows. That code is posted below. Since then l have been testing the workbook, before releasing it, and have now found a problem. The user can enter a 3 digit code in any of the 70 columns up to a maximum of 15. Therefore the maximum number of digits that can be concatenated is 45. However when that number of digits is concatenated the result ends up as a number raised to a power.(The function works fine when only 4 or 5 sets of 3 digits are entered) ie these digits when concatenated 100 101 200 201 203 219 225 300 301 400 401 500 550 600 900 end up being displayed as 100101200201203000000000000000000000000000000 and the value displayed in the formula bar is 1.00101200201203E+44 what l really need to see is a string as follows 100101200201203219225300301400401500550600 because in the next 15 columns l extract each of the 3 digit codes using functions (for example Left(A1,3) Mid(A1,4,3) etc) Any ideas as to whats going wrong and what the solution is please? Alternatively is it possible to 'loop thru' each of the 70 columns and when the 1st 3 digit value is found put it into say column 100, the 2nd 3 digit value in column 101 etc for each row in the dynamic range? xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx This works from row 1 to the last row and concatenates values from column 2 out, and drop the result in column A. Change to suit Sub ConcatenateRange() Dim val As String Dim iLastRow As Long Dim iLastCol As Long Dim i As Long Dim j As Long iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column val = "" For j = 2 To iLastCol val = val & Cells(i, j) Next j Cells(i, "A").Value = val Next i End Sub Kind regards Michael Beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bob Phillips - Please
The limit is Excel's number accuracy is 15 digits. See Tools/Options -
Calculation, Precision as Displayed. Use the ? balloon help. The 16th digit and up are truncated to 0 in the cell display. To get around this, format the cell as a custom text number, which allows up to 256 characters in the cell display. The format symbol is @. This will get you past your current number limit of 15, but the limit for all 70 columns still remains at 3 characters each. 70*3=210 concatenated characters. Bob's revision effectively gets you there, but your new limit is 256 concatenated characters. Mike F "Michael Beckinsale" wrote in message ... Bob, On the 13th Janaury you were kind enough to provide me with a function that concatenated values across approx 70 columns for a dynamic number of rows. That code is posted below. Since then l have been testing the workbook, before releasing it, and have now found a problem. The user can enter a 3 digit code in any of the 70 columns up to a maximum of 15. Therefore the maximum number of digits that can be concatenated is 45. However when that number of digits is concatenated the result ends up as a number raised to a power.(The function works fine when only 4 or 5 sets of 3 digits are entered) ie these digits when concatenated 100 101 200 201 203 219 225 300 301 400 401 500 550 600 900 end up being displayed as 100101200201203000000000000000000000000000000 and the value displayed in the formula bar is 1.00101200201203E+44 what l really need to see is a string as follows 100101200201203219225300301400401500550600 because in the next 15 columns l extract each of the 3 digit codes using functions (for example Left(A1,3) Mid(A1,4,3) etc) Any ideas as to whats going wrong and what the solution is please? Alternatively is it possible to 'loop thru' each of the 70 columns and when the 1st 3 digit value is found put it into say column 100, the 2nd 3 digit value in column 101 etc for each row in the dynamic range? xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx This works from row 1 to the last row and concatenates values from column 2 out, and drop the result in column A. Change to suit Sub ConcatenateRange() Dim val As String Dim iLastRow As Long Dim iLastCol As Long Dim i As Long Dim j As Long iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column val = "" For j = 2 To iLastCol val = val & Cells(i, j) Next j Cells(i, "A").Value = val Next i End Sub Kind regards Michael Beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calling Bob Phillips - Please
Hi Mike,
The cell can hold test strings of up to 32k characters. Regards, Peter "Mike Fogleman" wrote in message ... The limit is Excel's number accuracy is 15 digits. See Tools/Options - Calculation, Precision as Displayed. Use the ? balloon help. The 16th digit and up are truncated to 0 in the cell display. To get around this, format the cell as a custom text number, which allows up to 256 characters in the cell display. The format symbol is @. This will get you past your current number limit of 15, but the limit for all 70 columns still remains at 3 characters each. 70*3=210 concatenated characters. Bob's revision effectively gets you there, but your new limit is 256 concatenated characters. Mike F "Michael Beckinsale" wrote in message ... Bob, On the 13th Janaury you were kind enough to provide me with a function that concatenated values across approx 70 columns for a dynamic number of rows. That code is posted below. Since then l have been testing the workbook, before releasing it, and have now found a problem. The user can enter a 3 digit code in any of the 70 columns up to a maximum of 15. Therefore the maximum number of digits that can be concatenated is 45. However when that number of digits is concatenated the result ends up as a number raised to a power.(The function works fine when only 4 or 5 sets of 3 digits are entered) ie these digits when concatenated 100 101 200 201 203 219 225 300 301 400 401 500 550 600 900 end up being displayed as 100101200201203000000000000000000000000000000 and the value displayed in the formula bar is 1.00101200201203E+44 what l really need to see is a string as follows 100101200201203219225300301400401500550600 because in the next 15 columns l extract each of the 3 digit codes using functions (for example Left(A1,3) Mid(A1,4,3) etc) Any ideas as to whats going wrong and what the solution is please? Alternatively is it possible to 'loop thru' each of the 70 columns and when the 1st 3 digit value is found put it into say column 100, the 2nd 3 digit value in column 101 etc for each row in the dynamic range? xxxxxxxxx Code posted in January xxxxxxxxxxxxxxxxxx This works from row 1 to the last row and concatenates values from column 2 out, and drop the result in column A. Change to suit Sub ConcatenateRange() Dim val As String Dim iLastRow As Long Dim iLastCol As Long Dim i As Long Dim j As Long iLastRow = Cells.Find(What:="*", _ After:=Range("A1"), _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column val = "" For j = 2 To iLastCol val = val & Cells(i, j) Next j Cells(i, "A").Value = val Next i End Sub Kind regards Michael Beckinsale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
to Bob Phillips | Excel Discussion (Misc queries) | |||
For Bob Phillips | Excel Worksheet Functions | |||
Bob Phillips | Excel Programming | |||
To: Bob Phillips | Excel Programming | |||
Bob phillips?? | Excel Programming |