Consulting

Results 1 to 6 of 6

Thread: Copying cells from 2 row/Pasting into new column

  1. 01-08-2012, 06:52 AM #1
    ripkin900
    ripkin900 is offline
    VBAX Regular
    Joined
    Dec 2011
    Posts
    9
    Location

    Copying cells from 2 row/Pasting into new column

    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
    Reply With Quote Reply With Quote
  2. 01-08-2012, 09:56 AM #2
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base     VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Can you post a sample workbook?
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  3. 01-08-2012, 03:31 PM #3
    ripkin900
    ripkin900 is offline
    VBAX Regular
    Joined
    Dec 2011
    Posts
    9
    Location
    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....
    Attached Files Attached Files
    Reply With Quote Reply With Quote
  4. 01-08-2012, 05:08 PM #4
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [vba]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
    [/vba]

    or, with no extra column required
    [VBA]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
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
  5. 02-04-2012, 09:41 PM #5
    ripkin900
    ripkin900 is offline
    VBAX Regular
    Joined
    Dec 2011
    Posts
    9
    Location

    Thanks so much

    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
    Reply With Quote Reply With Quote
  6. 02-05-2012, 12:23 PM #6
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    [VBA]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[/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.

    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules