PDA

View Full Version : Copying cells from 2 row/Pasting into new column



ripkin900
01-08-2012, 06:52 AM
Hi, I have a text file...
When I import it into Excel the one column data fills 2 rows.....

SO I am looking for a Macro to take cells H1, H2 combine them into a new column, and put them in cell J1

So then h3, h4 will go into J3

It is a paginated text file, so every 20 rows there is the headers which can be ignored....

So I think a do while would work but I cannot figure it out, any help would be greatly aprecciated

Sample text file:
Dept: IL_DF_INSP IL DF 1ST PIECE INSPECTION

|---------------Scheduled---------------|
Operation Start Operation Compl Job Compl Build |----Operation----| |-----------Quantity-----------|
Date Time Date Time Date Job Assembly Seq Seq Description Remaining In Queue Running
--------------- --------------- --------- --------------- --------------- ----- ---- -------------- ---------- ---------- ----------
16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711085 675.181230.3012 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711086 675.181230.3022 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711088 675.181230.3032 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711089 675.181230.3042 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711090 675.181230.3052 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711094 675.181230.3082 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711095 675.181230.3092 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711096 675.181230.3102 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711097 675.181230.3112 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:20 16-MAR-12 06:25 19-MAR-12 6711098 675.181230.3122 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:54 16-MAR-12 06:59 19-MAR-12 6711091 675.181230.3062 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

16-MAR-12 02:54 16-MAR-12 06:59 19-MAR-12 6711093 675.181230.3072 40 1ST PIECE 60.00 0.00 0.00
INSPECTION -
IL

18-MAR-12 00:34 19-MAR-12 04:36 21-MAR-12 6709534 675.115499.3012 40 1ST PIECE 14.00 0.00 0.00
INSPECTION -
IL
XXXXXXXXXXXXX USA Report Date: 07-JAN-2012 06:09
Sort By Operation start date Discrete Job Dispatch Report Page: 413 of 4957
Include Unreleased Jobs: Yes
Dates From 01-JAN-06 To 30-JUN-12



Dept: IL_DF_INSP IL DF 1ST PIECE INSPECTION

|---------------Scheduled---------------|
Operation Start Operation Compl Job Compl Build |----Operation----| |-----------Quantity-----------|
Date Time Date Time Date Job Assembly Seq Seq Description Remaining In Queue Running
--------------- --------------- --------- --------------- --------------- ----- ---- -------------- ---------- ---------- ----------
18-MAR-12 00:34 19-MAR-12 04:36 21-MAR-12 6709535 675.115499.3022 40 1ST PIECE 14.00 0.00 0.00
INSPECTION -
IL

18-MAR-12 00:34 19-MAR-12 04:36 21-MAR-12 6709541 675.115499.3082 40 1ST PIECE 14.00 0.00 0.00
INSPECTION -
IL

18-MAR-12 00:34 19-MAR-12 04:36 21-MAR-12 6709542 675.115499.3092 40 1ST PIECE 14.00 0.00 0.00
INSPECTION -
IL

18-MAR-12 00:46 19-MAR-12 04:48 21-MAR-12 6709536 675.115499.3033 40 1ST PIECE 14.00 0.00 0.00

mdmackillop
01-08-2012, 09:56 AM
Can you post a sample workbook?

ripkin900
01-08-2012, 03:31 PM
So this is the sample file...

Column H has the data in question

SOme are 1 line, some are 2 line and some are 3 and some are 4, I would like to copy data in column H, into a seperate column L, M, N, O.....I cna then join them together....

mdmackillop
01-08-2012, 05:08 PM
Option Explicit

Sub Data()
Dim r As Range, cel As Range, c As Range
Dim txt As String
Columns("J:J").Insert
Set r = Columns(1).SpecialCells(2, 1).Offset(, 7)
For Each cel In r
If cel.Offset(1) = "" Then
cel.Offset(, 2) = cel
Else
txt = ""
For Each c In Range(cel, cel.End(xlDown))
txt = txt & " " & c
Next
cel.Offset(, 2) = txt
End If
Next
Columns("J:J").EntireColumn.AutoFit
End Sub


or, with no extra column required
Sub Data2()
Dim r As Range, cel As Range, c As Range
Dim txt As String

Set r = Columns(1).SpecialCells(2, 1).Offset(, 7)
For Each cel In r
If cel.Offset(1) = "" Then
'do nothing
Else
txt = ""
For Each c In Range(cel, cel.End(xlDown))
txt = txt & " " & c
c.Clear
Next
cel = txt
End If
Next
Columns("H:H").EntireColumn.AutoFit
End Sub

ripkin900
02-04-2012, 09:41 PM
This worked perfect...

Sorry I have not writtten back sooner thanking you but I have been super busy...

One little tweek....

A few columns have been added and the data to work with is now in column J and not H

I read up on the Offset function and tried to modify your code but have not been able to figure it out, so could you please tell me how to modify the code to work with Col J???

Thanks so much in advance...

Jeff

mdmackillop
02-05-2012, 12:23 PM
Sub Data2()
Dim r As Range, cel As Range, c As Range
Dim txt As String

Set r = Columns(1).SpecialCells(2, 1).Offset(, 9)
For Each cel In r
If cel.Offset(1) = "" Then
'do nothing
Else
txt = ""
For Each c In Range(cel, cel.End(xlDown))
txt = txt & " " & c
c.Clear
Next
cel = txt
End If
Next
Columns("J:J").EntireColumn.AutoFit
End Sub