Wrap Text to Cell Below, not within same cell

[Fay Girls]

Hello!

I have this code:
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

The way it is coded now, the text will wrap to the cell below (which is what I want), but it splits words when it hits 63 characters. Does anyone know how to change it so it wraps full words (<=63 characters)???? Please help!

Thank you!

Autumn

 

[Snakehips]

Autumn,

Try modifying your code as below....

Private Sub Worksheet_Change(ByVal Target As Range)
Dim strTarget As String, lngLen As Long, intRep As Integer, i As Integer, intSplit As Integer, myChar As String
intSplit = 63  '*** no longer constant
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
If Len(Target.Value) > intSplit Then
 
'Work back thro' string to find a space
Do Until myChar = " "
myChar = Mid$(Target.Value, intSplit, 1)
intSplit = intSplit - 1
Loop
 
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

Let me know if that helps.

Tony

 

[Fay Girls]

Hello! Thanks for trying to help. The text isn't wrapping to the cell below. Any other suggestions? The code I posted initially wrapped the text to the cell below after 63 characters, but it would split a word in half. Thanks for your help!

Autumn

 

[Snakehips]

Autumn,

Unless I have misunderstood, this code appears to do the job ok at my end.

Just to confirm....

You are entering a string into a cell in column A.
Currently, if that string exceeds 63 characters then, when you hit enter, the characters in excess of 63 appear in the cell below. However, the fixed value of 63 means that sometimes words are split, part in entry cell and part below.

The only thing that I have changed in the original code that you posted is to adjust the value of variable intSplit. Instead of being the constant 63 as was it is adjusted to the point in the input string, of the first space to the left of the 63rd character. Your original code then splits and wraps the string at that point.

If there are still issues then can you post back with an exact copy of your new code.
Tony

 

[Michael M]

Works fine for me Tony.
The only thing I might mention to Autumn, is that the code will split the text at the nearest point before 63 characters.....So, if the last full word ends at 59 characters, that will be where it wraps.

 

[Fay Girls]

Oh my gosh! It worked. Initially, I put it in the existing excel doc that I had the other code in. When I put in the above code, it just didn't work. After you both confirmed to me that it worked for you....I tried it in a new file and waaalah! It worked! Thank you, thank you, thank you!!!

Autumn