Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: How do I create a column of fixed width data?
I have a column that combines the data of every row and delimates by a | .. Instead of deliminating by |, I want to make it fixed width depending on the field. Here is what I have for the delimited by |. Any ideas on how to code for the fixed width? For example, Column 1 a max of 1 character, Column 2 will be between 2-5, Column 3 will be 6-20, Column 4 will be 21-25, etc. If the data in the field is less than the total fixed amount (IE: column 3 goes to 10 and there is 10 left to reach 20), I need the void to be blanks and for column 4 to begin at 21. Here's what I have for the delimited code and it works. I just need the fixed part going now. Thanks much everyone! Code: -------------------- Private Sub finalOutput(no As Long) Dim c1 As String Dim c2 As String Dim c3 As String Dim c4 As String Dim c5 As String Dim c6 As String Dim c7 As String Dim c8 As String Dim c9 As String Dim c10 As String Dim c11 As String Dim c12 As String Dim c13 As String Dim c14 As String Dim c15 As String Dim c16 As String Dim c17 As String Dim c18 As String Dim c19 As String Dim c20 As String Dim c21 As String Dim c22 As String Dim c23 As String Dim c24 As String Dim colCount As Long Dim rowCount As Long Dim row As Long Worksheets(3).Activate colCount = countCs(no) rowCount = countRs(no) For row = 1 To rowCount c1 = CStr(Worksheets(3).Cells(row, colCount - 23).Value) c2 = CStr(Worksheets(3).Cells(row, colCount - 22).Value) c3 = CStr(Worksheets(3).Cells(row, colCount - 21).Value) c4 = CStr(Worksheets(3).Cells(row, colCount - 20).Value) c5 = CStr(Worksheets(3).Cells(row, colCount - 19).Value) c6 = CStr(Worksheets(3).Cells(row, colCount - 18).Value) c7 = CStr(Worksheets(3).Cells(row, colCount - 17).Value) c8 = CStr(Worksheets(3).Cells(row, colCount - 16).Value) c9 = CStr(Worksheets(3).Cells(row, colCount - 15).Value) c10 = CStr(Worksheets(3).Cells(row, colCount - 14).Value) c11 = CStr(Worksheets(3).Cells(row, colCount - 13).Value) c12 = CStr(Worksheets(3).Cells(row, colCount - 12).Value) c13 = CStr(Worksheets(3).Cells(row, colCount - 11).Value) c14 = CStr(Worksheets(3).Cells(row, colCount - 10).Value) c15 = CStr(Worksheets(3).Cells(row, colCount - 9).Value) c16 = CStr(Worksheets(3).Cells(row, colCount - 8).Value) c17 = CStr(Worksheets(3).Cells(row, colCount - 7).Value) c18 = CStr(Worksheets(3).Cells(row, colCount - 6).Value) c19 = CStr(Worksheets(3).Cells(row, colCount - 5).Value) c20 = CStr(Worksheets(3).Cells(row, colCount - 4).Value) c21 = CStr(Worksheets(3).Cells(row, colCount - 3).Value) c22 = CStr(Worksheets(3).Cells(row, colCount - 2).Value) c23 = CStr(Worksheets(3).Cells(row, colCount - 1).Value) c24 = CStr(Worksheets(3).Cells(row, colCount - 0).Value) Worksheets(3).Cells(row, colCount + 1).Value = CStr(c1 + "| " + c2 + "| " + c3 + "| " + c4 + "| " + c5 + "| " + c6 + "| " + c7 + "| " + c8 + "| " + c9 + "| " + c10 + "| " + c11 + "| " + c12 + "| " + c13 + "| " + c14 + "| " + c15 + "| " + c16 + "| " + c17 + "| " + c18 + "| " + c19 + "| " + c20 + "| " + c21 + "| " + c22 + "| " + c23 + "| " + c24) Next row End Sub Private Function countCs(no As Long) As Long countCs = Worksheets(3).UsedRange.Columns.Count End Function Private Function countRs(no As Long) As Long countRs = Worksheets(3).UsedRange.Rows.Count End Function -------------------- My output looks like: letter | year | name | address So with the fixed width, it should be: A2005Limshady 123 N Blah 1234567890123456789012345678 I hope this makes sense. Thanks again! -- limshady411 ------------------------------------------------------------------------ limshady411's Profile: http://www.excelforum.com/member.php...o&userid=28711 View this thread: http://www.excelforum.com/showthread...hreadid=493636 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help: How do I create a column of fixed width data?
What I'm trying to do is basically the inverse of the Data -- Text to Columns function. I hope this helps and you guys can help me. Thanks again! -- limshady411 ------------------------------------------------------------------------ limshady411's Profile: http://www.excelforum.com/member.php...o&userid=28711 View this thread: http://www.excelforum.com/showthread...hreadid=493636 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fixed Column Width Export | Excel Discussion (Misc queries) | |||
Column fixed width | Excel Discussion (Misc queries) | |||
Text to column, fixed width | Charts and Charting in Excel | |||
Text to column, fixed width | Excel Discussion (Misc queries) | |||
How do I create a "fixed-width" datafile? | Excel Discussion (Misc queries) |