+ Reply to Thread
Results 1 to 7 of 7

Word wrapping to the next row

  1. #1
    Registered User
    Join Date
    10-28-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Word wrapping to the next row

    Don't know if there is a work around for this, but what I would like to do is have excel word wrap not within the cell but carry down to the cell in the next row. Essentially what I am doing using excel to create a report that others can fill out. Does anyone know what I need to do?
    Last edited by j_talbain; 10-28-2010 at 12:27 PM. Reason: title change

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Bending excel to other uses...

    Welcome to the forum.

    That's just not the way Excel works -- or Word for text in tables, for that matter.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-28-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Bending excel to other uses...

    Forgive me for my ignorance but I don't know what to call my problem or I'd be more specific. Also I understand excel doesn't work the exact way I want it to, but I'm just trying to see if there is a workaround to 'trick' it into doing what I want. Unless you have a better idea for designing the document in another program that will allow me line everything like excel.

  4. #4
    Registered User
    Join Date
    10-28-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Bending excel to other uses...

    Someone gave me this code thinking that it might help, but I have no idea what to do with it.

    Please Login or Register  to view this content.
    Explanation


    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim strTarget As String, lngLen As Long, intRep As Integer, i As Integer
    Const intSplit As Integer = 63

    'defines the range into which a relevant value might be entered (I assumed column A),
    'and checks that the number of cells being changed is not >1.
    'Reason for this is that it can get complicated trying to
    'cope with a number of changes in one go.
    If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub

    'checks the length of the value which has just been changed.
    'If this is greater than our constant of 63 then...
    If Len(Target.Value) > intSplit Then
    'assign the string to a variable (for neatness)
    strTarget = Target.Value
    'assign the length of the string to a variable
    lngLen = Len(strTarget)
    'work out the number of cells over which to split the value:
    'essentially the length of the string divided by 63 and rounded up to the nearest 1
    intRep = Int(lngLen / intSplit) + 1
    'loop through the string that number of times
    For i = 1 To intRep
    'offset from the changed cell downwards, and populate that cell with
    'the string which is 63 characters long and whose starting character
    'is a multiple of 63, starting at 0
    Target.Offset(i - 1, 0).Value = Mid(strTarget, (i - 1) * intSplit + 1, intSplit)
    'loop to the next iteration
    Next i

    End If
    End Sub
    Hope that helps?
    Last edited by j_talbain; 10-28-2010 at 12:42 PM. Reason: typos

  5. #5
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Bending excel to other uses...

    Go to your first post and EDIT and Go Advanced and change the title to something like "Word wrapping to the next row." (titles should try to summerize the task or problem).
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  6. #6
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: Word wrapping to the next row

    right click on the sheet tab and choose "View Code" Then paste the code that was given to you into the window that shows up. Then close that window (no need to save) and make a change in column A of your worksheet and see what happens.

  7. #7
    Registered User
    Join Date
    10-28-2010
    Location
    usa
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Word wrapping to the next row

    After experimenting with the code a bit it seems to do the job, if a bit strangely... The text will be kept in one cell until you hit enter. Then it will divide the lines up appropriately. I didn't spend to much time with so I'm not sure how it divides the words up. Thanks to ChemistB for the tip on an easy way to enter the programming side of excel. And a huge thank you to the original author of the article that someone sent me.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1