Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Macro to cut and paste

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    102
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Good Morning:

    I am looking to create a macro to divide a cell (delimit) via a macro versus manually. The file comes in with a cell containing approx. 380 characters. I need to create a column for every 7 characters within that cell and whatever remains after the 371st character would populate the last cell of that row. In addition, the macro should loop until the cell is completely spit up by 7's then continue to the next row until a blank row is encountered. Any ideas (or examples) to get me started?

    This is the first message I have ever sent for help, so am not sure of the procedure. Any assistance would be appreciated. Thanks; have a good day.



  2. #2
    New Member
    Join Date
    Apr 2002
    Location
    dallas, tx
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try this; if problems, just shout:

    Sub Macro1()

    Dim textstring, textmod As String
    Dim column As Integer

    Range("a1").Select
    textstring = ActiveCell.Formula
    Do Until textstring = ""
    textmod = textstring
    column = 1
    Do Until textmod = ""
    If (Len(textmod) > 7) Then
    ActiveCell.Offset(0, column).Formula = Left(textmod, 7)
    textmod = Right(textmod, Len(textmod) - 7)
    column = column + 1
    Else
    ActiveCell.Offset(0, column).Formula = textmod
    textmod = ""
    End If
    Loop
    ActiveCell.Offset(1, 0).Select
    textstring = ActiveCell.Formula
    Loop

    End Sub

  3. #3
    New Member
    Join Date
    Apr 2002
    Location
    dallas, tx
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Added a counter so that after 53 columns of 7 letters the 54th column will be loaded with whatever text is remaining in your string. (Only one line has been changed from the previous code) Again, shout if this doesn't help:

    Sub Macro1()

    Dim textstring, textmod As String
    Dim column As Integer

    Range("a1").Select
    textstring = ActiveCell.Formula
    Do Until textstring = ""
    textmod = textstring
    column = 1
    Do Until textmod = ""
    If (Len(textmod) > 7) And column < 54 Then
    ActiveCell.Offset(0, column).Formula = Left(textmod, 7)
    textmod = Right(textmod, Len(textmod) - 7)
    column = column + 1
    Else
    ActiveCell.Offset(0, column).Formula = textmod
    textmod = ""
    End If
    Loop
    ActiveCell.Offset(1, 0).Select
    textstring = ActiveCell.Formula
    Loop

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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