Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two text lines into one? Macro help needed
I have a text file I am moving into excel. Some rows start with 02 and
some with 03. What I want to happen is that excel will write an 02 row into excel, then when it sees an 03 row it will put it at the end of the 02 row above it. For some reason the 03 rows aren't being written onto the spread sheet, but the 02 rows are fine. I would be happy if the 03 row just began at the end of the 02 even if it were on the line down from it. So say row 1 ends at AA then row 2 will start at AB Here is what I have: Sub VariedLineImport() Dim ResultStr As String Dim FileName As String Dim FileNum As Integer Dim Counter As Double FileName = Application.GetOpenFilename If FileName = "" Then End FileNum = FreeFile() Open FileName For Input As #FileNum Application.ScreenUpdating = False Workbooks.Add Template:=xlWorksheet Counter = 1 Do While Seek(FileNum) <= LOF(FileNum) 'Store One Line Of Text From File To Variable Line Input #FileNum, ResultStr 'Store Variable Data Into Active Cell 'Etc If Left(ResultStr, 2) = "02" Then 'do the splitting here, like Cells(Counter, 1).Value = Mid(ResultStr, 3, 9) Cells(Counter, 2).Value = Mid(ResultStr, 12, 20) Cells(Counter, 3).Value = Mid(ResultStr, 32, 5) Cells(Counter, 4).Value = Mid(ResultStr, 37, 15) Cells(Counter, 5).Value = Mid(ResultStr, 52, 15) Cells(Counter, 6).Value = Mid(ResultStr, 67, 25) Cells(Counter, 7).Value = Mid(ResultStr, 92, 40) Cells(Counter, 8).Value = Mid(ResultStr, 132, 40) Cells(Counter, 9).Value = Mid(ResultStr, 172, 40) Cells(Counter, 10).Value = Mid(ResultStr, 212, 30) Cells(Counter, 11).Value = Mid(ResultStr, 242, 5) Cells(Counter, 12).Value = Mid(ResultStr, 247, 11) Cells(Counter, 13).Value = Mid(ResultStr, 258, 2) Cells(Counter, 14).Value = Mid(ResultStr, 260, 5) Cells(Counter, 15).Value = Mid(ResultStr, 265, 10) Cells(Counter, 16).Value = Mid(ResultStr, 275, 1) Cells(Counter, 17).Value = Mid(ResultStr, 276, 3) Cells(Counter, 18).Value = Mid(ResultStr, 279, 40) Cells(Counter, 19).Value = Mid(ResultStr, 319, 10) Cells(Counter, 20).Value = Mid(ResultStr, 329, 10) Cells(Counter, 21).Value = Mid(ResultStr, 339, 3) Cells(Counter, 22).Value = Mid(ResultStr, 364, 9) Cells(Counter, 23).Value = Mid(ResultStr, 373, 10) 'Etc ElseIf Left(ResultStr, 2) = "03" Then 'do the splitting here, like Cells(Counter, 24).Value = Mid(ResultStr, 383, 9) Cells(Counter, 25).Value = Mid(ResultStr, 392, 20) Cells(Counter, 26).Value = Mid(ResultStr, 396, 4) Cells(Counter, 27).Value = Mid(ResultStr, 400, 8) Cells(Counter, 28).Value = Mid(ResultStr, 408, 4) Cells(Counter, 29).Value = Mid(ResultStr, 412, 3) Cells(Counter, 30).Value = Mid(ResultStr, 414, 2) Cells(Counter, 31).Value = Mid(ResultStr, 424, 10) Cells(Counter, 32).Value = Mid(ResultStr, 434, 10) Cells(Counter, 33).Value = Mid(ResultStr, 444, 8) Cells(Counter, 34).Value = Mid(ResultStr, 452, 3) 'Etc 'And so on for different starting values End If 'Increment the Counter By 1 Counter = Counter + 1 Loop 'Close The Open Text File Close End Sub Others have helped me get this far and it has been much appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two text lines into one? Macro help needed
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge two text lines into one? Macro help needed
Mary,
The "03" rows aren't being imported because you're using The IF-ELSEIF structure. Can I suggest you try this: SELECT CASE Left(ResultStr, 2) CASE "02" do the "02" stuff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A macro for deleting lines of text | Excel Worksheet Functions | |||
merge columns into single report - macro needed | Excel Discussion (Misc queries) | |||
HELP Needed, Word mail merge breaks when saved as.. from an Excel Macro | Excel Programming | |||
How do I merge 3 lines of text to show in one row? | Excel Discussion (Misc queries) | |||
Wrap and merge text will not show all lines | Excel Discussion (Misc queries) |