Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
Hi there,
I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
If there's no problem with the txt overlapping some of the following
columns, you could merge C1:K1 (or whatever suits the layout) and then allow the text to wrap. "Stewart Allen" wrote in message ... Hi there, I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
Hi,
I also have same problem in splitting long sentence into several rows, and add them to seperate rows of active worksheets. Could anybody help me ? Thx! Roger "Stewart Allen" ¦b¶l¥ó ¤¤¼¶¼g... Hi there, I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
I am working on the same problem. I got nowhere trying to substitute
the vbCrlf character in the string. That seems only to work if you display the cell contents in a text box. I think what we need to do is find a way to code the sequence of keystrokes that enters the carriage return/line feed into the cell for display. So how do we code the CTRL-ALT-ENTER keystrokes? Regards, Pat |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
Thanks, I'll give that a go.
"Ian Coates" wrote in message ... If there's no problem with the txt overlapping some of the following columns, you could merge C1:K1 (or whatever suits the layout) and then allow the text to wrap. "Stewart Allen" wrote in message ... Hi there, I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
Yes the merging of the cells worked but one more problem has surfaced, how
to automatically change the row height. The bottom of my code is now: MyR.Cells = Temp MyR.VerticalAlignment = xlVAlignTop Range(Cells(1, 3), Cells(1, 11)).MergeCells = True MyR.WrapText = True MyR.Rows.AutoFit <<< This line doesn't work How do I get the row to automatically adjust its height to display all the text when wrapping? Thanks Stewart "Ian Coates" wrote in message ... If there's no problem with the txt overlapping some of the following columns, you could merge C1:K1 (or whatever suits the layout) and then allow the text to wrap. "Stewart Allen" wrote in message ... Hi there, I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrapping Text with VBA
This is getting a bit deep for me. Having merged the cells, do you need to
Set MyR again? I know that Excel addresses merged cells as the top left cell in the group, but I don't know if VBA sees it the same way. Perhaps you have to set the range as if the cells were discreet. Set MyR = ActiveSheet.Range("C1:M1") It's just a guess. Ian "Stewart Allen" wrote in message ... Yes the merging of the cells worked but one more problem has surfaced, how to automatically change the row height. The bottom of my code is now: MyR.Cells = Temp MyR.VerticalAlignment = xlVAlignTop Range(Cells(1, 3), Cells(1, 11)).MergeCells = True MyR.WrapText = True MyR.Rows.AutoFit <<< This line doesn't work How do I get the row to automatically adjust its height to display all the text when wrapping? Thanks Stewart "Ian Coates" wrote in message ... If there's no problem with the txt overlapping some of the following columns, you could merge C1:K1 (or whatever suits the layout) and then allow the text to wrap. "Stewart Allen" wrote in message ... Hi there, I'm trying to split long text so it doesn't go off the page sideways and doesn't wrap inside the cell so that the cell only displays about 10 characters sideways and the rest of the text reads downwards. Type a sentence of about 150 character into cell C1 and the text will display to the O or P column. When you turn on the warp text for cell C1, the cell increases in height with all the text in the cell. What I'm after is split the text with VBA at around the 90 - 100 character mark and drop the rest of the text below the first line but still in the same cell. I could split the text and place the second line in the cell below C2 but really want to split the text and keep it inside the same cell. The code I've used for testing is: Dim str as String Dim Temp as String Dim intPos as Integer Dim intLen as Integer Dim MyR as Range intLen = Len(str) For intPos = 1 To intLen If intPos Mod 50 = 0 Then Temp = Temp & vbCrLf Temp = Temp & Mid(str, intPos, 1) Else Temp = Temp & Mid(str, intPos, 1) End If Next Set MyR = ActiveSheet.Range("C1") MyR = Temp I've used 50 at the cut off for testing and insert a carriage return and line feed every 50 characters. All this does is insert a square character every 50 characters but still displays the text on one line. If I add the code "MyR.WrapText = True", all the text wraps inside the cell but I have to resize the cell width to display the text how I like. I don't want to resize the cell because this will distort the contents in the cell above. Any suggestions? Thanks Stewart |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prevent a long string of text from wrapping in a text fil | Excel Discussion (Misc queries) | |||
Wrapping text | Excel Discussion (Misc queries) | |||
text wrapping when text is pasted in excel | Excel Discussion (Misc queries) | |||
I am using wrap text in excel, so why isn't all my text wrapping? | Excel Discussion (Misc queries) | |||
wrapping text from one row to the next | Excel Discussion (Misc queries) |