How to wrap text to next row

[mthelm85]

I've been searching the internet for this for about 2 hours now and I cannot figure out how to do it. Due to the format of my worksheet, a textbox won't work, wrapping the text in the same cell won't work, etc. I need to be able to wrap my text from one row into the row directly below it. For example, if I'm typing in cell A18, I need the text to auto wrap to cell A19 after 50 characters or so. The ONLY thing I have been able to find is this code that doesn't work:

Please Login or Register  to view this content.

PLEASE HELP ME!!

[HSV]

The code works fine to me if there are spaces in it.

[xladept]

Hi mthelm85,

Here's a parser:

Please Login or Register  to view this content.

it will parse whatever text is in the active cell to be just n characters
and write them immediately below the source - if you want to overwrite the
source data just change the for loop to range from 0 to j.

[mthelm85]

The code works fine to me if there are spaces in it.

Where do you put the spaces?

[xladept]

Hi mthelm85,

Spaces near the break point are dropped - so the next line doesn't start with a space.

(BTW to overwrite you'd have to change the S(i) to S(i+1) also)

[mthelm85]

Hi mthelm85,

Spaces near the break point are dropped - so the next line doesn't start with a space.

(BTW to overwrite you'd have to change the S(i) to S(i+1) also)

Sorry man but that's Greek to me. Can someone paste the code exactly how it should be pasted into Excel so that it will work? Also, all I need to do is right click the sheet tab, go to view code, then paste it into a module, correct?

[HSV]

Type some text over 50 characters in cell A18.

[mthelm85]

Type some text over 50 characters in cell A18.

Thanks for the help but I am getting the same error message with your file that I was getting with my original. I type over 50 characters and then press enter and it gives me runtime error '13' and the option to "end" or "debug." When I tell it debug, Microsoft Visual Basic opens and the line "a = Application.Find(" ", Rng, a + 1)" is highlighted in yellow.....????

[HSV]

If there are no spaces in the word you type, it gives runtime error '13'.

Please Login or Register  to view this content.

[xladept]

You'd need to invoke the code with the "n" argoment i.e. 50
After you put the code in a module, you could record a key macro i.e. ^P
Then using the key macro as a wrapper invoke the Parser

Please Login or Register  to view this content.

[mthelm85]

If there are no spaces in the word you type, it gives runtime error '13'.

Please Login or Register  to view this content.

AWESOME, dankjewel!! One last thing. That code works great if I just want the text in A18 to wrap to A19 but how do I get the text in A19 to wrap to A20, etc.? Would it be Range ("A18:A24"), for example?

[HSV]

Change the code into.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[HSV]

dankjewel!!

Very nice.

[mthelm85]

Very nice.

Je bent de beste, het werkte perfect.

[clen0012]

AWESOME, dankjewel!! One last thing. That code works great if I just want the text in A18 to wrap to A19 but how do I get the text in A19 to wrap to A20, etc.? Would it be Range ("A18:A24"), for example?

I just stumbled upon this thread as I was aiming to accomplish the same thing as mthelm85 but with an additional caveat. I have an excel template that I am setting up for our users to paste text from other documents (word, excel, pdfs, etc.) into & if the length of the text is over 80 (in my case) characters for the additional text/words to show up in the next row (not "wrap" the text, as this resizes the row height). I was able to accomplish this with HSV's VBA code referenced above, but I am finding out that it does not always work when copying text from certain source formats (excel & word) & then pasting it into the workbook. Even when pasting with "Match Destination Format" I am still encountering VBA & format errors. Any suggestions or solutions to this would be greatly appreciated as I am a complete novice when it comes to coding or VBA!

Please Login or Register  to view this content.

That is what I have been employing so far with only some success. Thanks in advance for any help!