auto wrap text to cell below after 63 characters

[keithmct]

greetings all,

I have an invoice type spreadsheet which gathers info from other sheets. I want to be able to type in the description section of the invoice for work completed and after 63 characters automatically drop down to the cell below. Is that possible?
I've tried :
- wrapping text in same cell - don't like that outcome
- merging the whole section into one cell and having text start at the top, but I still need lines underneath to be available for another macro.

 

[Yard]

Try this in the Worksheet module:

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
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
If Len(Target.Value) > intSplit Then
    strTarget = Target.Value
    lngLen = Len(strTarget)
    intRep = Int(lngLen / intSplit) + 1
    For i = 1 To intRep
        Target.Offset(i - 1, 0).Value = Mid(strTarget, (i - 1) * intSplit + 1, intSplit)
    Next i
    
End If
End Sub

edit: just tweaked (to make it work properly I think!)

 

[keithmct]

didn't work. I get an overflow error.
More info:
I have a section of the sheet from A30:J30 merged into one cell and copied down until row 42. Should I change any of the code to suit? (I tried all sorts of combinations)

 

[Yard]

Which line gives the error?

What do you mean by

merged into one cell and copied down until row 42

 

[keithmct]

sorry had a typing error but now get a runtime error 11 :division by zero

in line

intRep = Int(lngLen/intSplit) +1

have to go to work. thanks for your help so far.

 

[Yard]

What do you mean, "typing error"? Have you changed the code?

And what did you enter into the cell to get the "division by zero" message?

Which version of Excel are you using?

 

[keithmct]

Yard, I'm home from work now and found another typing error from this morning (maybe I was still half asleep). What you've given me works perfectly. Thanks heaps.

Keith.

ps. any chance you could PM me and explain exactly what your code does. This way I can learn and not just depend on everybody else.

 

[keithmct]

I had to transpose your code from 1 PC to another and made 2 typing errors in the process.

I'm using Vista/Office2007, but my spreadsheet is working under 2003 compatability mode.

It's all good now.

 

[Yard]

Yard, I'm home from work now and found another typing error from this morning (maybe I was still half asleep). What you've given me works perfectly. Thanks heaps.

Keith.

ps. any chance you could PM me and explain exactly what your code does. This way I can learn and not just depend on everybody else.

Good, glad it works. Commented version below, hope it helps:

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?

 

[keithmct]

after some more experimentation, I have issues with this solution. Sometimes a word will be cut up (without a hyphen of course) and pasted to next row. Sometimes the next row starts with a blank.
I would really like it to word wrap, but not in the same cell, to the cell beneath.