MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hard Returns in Macro


Posted by Alan G on December 18, 2001 3:44 PM

I want to concatenate two items with a carriage return between them. i.e.:

ActiveCell.Value = ActiveCell.Offset(0,1) & <CR> & ActiveCell.Offset(0,2)

Can anyone tell me how to do so?


Posted by Tom Urtis on December 18, 2001 3:55 PM

Is this what you want?

Example, if you have "John" in B1, and "Smith" in C1, and your active cell is A1, then try:

ActiveCell.Value = ActiveCell.Offset(0, 1).Value & Chr(10) & ActiveCell.Offset(0, 2).Value

This code would is all be on a single line in your module. The return in A1 would be:

John
Smith

If I misunderstood your question, please repost.

HTH

Tom Urtis

Posted by Alan Graham on December 19, 2001 5:51 AM

Re: Is this what you want?

If you want a quick way of manually checking, use Find & replace, replace it with ZZ099, and then replace it back again. The first time you do it should show you how many replacements were made.