Find & Replace

[Pauba]

Hello Guys:

Excel 2000.

I need to find a specific text, a flag (eg.: "<LineBreak>"), and replace it
with a real manual line break . Is this something possible to do?

Thanks,

P.

 

[Dave Peterson]

You could use a little macro that searches for that string, then inserts a
pagebreak when it finds it. (I inserted a horizontal pagebreak.)

Option Explicit
Sub testme01()

Dim myStr As String
Dim FoundCell As Range

myStr = "<LineBreak>"

With ActiveSheet
Do
Set FoundCell = .Cells.Find(what:=myStr, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, lookat:=xlPart, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.ClearContents
If FoundCell.Row > 1 Then
.HPageBreaks.Add before:=FoundCell
End If
End If

Loop
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Pauba]

Hello Dave:
That's a beginning, but I really do need a line break inside the cell. The
ASCI-013 code (Carriage Return).
I need to begin a new line inside the same cell.

Thanks,

P.

 

[Ragdyer]

Select the cells to change, then:
<Edit> <Replace>,
In the "Find What" window, enter:
<LineBreak>
In the "Replace With" window, enter:
<Alt>0010
Using the numbers from the Num keypad, *not* the numbers under the function
keys.
Don't worry about not seeing anything in the "Replace" window.
Then click "Replace All".

 

[Ragdyer]

Dave,
Is page break and line break the same thing in code?<g>

 

[Dave Peterson]

er, it is to me.

Thanks for posting a correct answer.

 

[Pauba]

Select the cells to change, then:
<Edit> <Replace>,
In the "Find What" window, enter:
<LineBreak>
In the "Replace With" window, enter:
<Alt>0010
Using the numbers from the Num keypad, *not* the numbers under the function
keys.
Don't worry about not seeing anything in the "Replace" window.
Then click "Replace All".

You are a genius!
That's exactly what I need! It never ocurred to me the ALT key, even
thoug I use it in when typing inside word.
Thank you VERY much.

And Dave, thank you you too, but his solution is much easier...

P.

 

[Ragdyer]

Appreciate the feed-back.

 

[Ragdyer]

Quite seriously Dave, do you have any idea what the heck is the difference
between those two different sets of keys?

Don't they both produce the same codes?

 

[Dave Peterson]

A page break will force a new page when printed.

A line break will force a new line within the cell.

Alt-enter (chr(10)/vblf) is just another character in the cell.

A long time ago, old text editors would use ctrl-L (chr(12)) to force a newpage
in a text document. (Is that what you meant?)

Quite seriously Dave, do you have any idea what the heck is the difference
between those two different sets of keys?

Don't they both produce the same codes?

 

[Dave Peterson]

Ragdyer's solution has the added benefit of being a correct answer to your
question.

My suggestion is a correct answer to a different question <vbg>.

 

[Ragdyer]

NO !<g>
I was talking about the Num keypad as opposed to the numbers under the
function keys producing the CHAR() characters.

 

[Dave Peterson]

I understand that pressing those keys sends a different code from the keyboard
to the operating system.

I don't know much more than that.

NO !<g>
I was talking about the Num keypad as opposed to the numbers under the
function keys producing the CHAR() characters.