Find & Replace

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.

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 wrote:
>
> 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

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.


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > 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

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".
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pauba" <[email protected]> wrote in message
news:[email protected]...
> 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,
Is page break and line break the same thing in code?<g>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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 wrote:
> >
> > 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

er, it is to me.

Thanks for posting a correct answer.



Ragdyer wrote:
>
> Dave,
> Is page break and line break the same thing in code?<g>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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 wrote:
> > >
> > > 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

--

Dave Peterson

"Ragdyer" <[email protected]> wrote in message
news:%[email protected]...
> 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.

Appreciate the feed-back.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Pauba" <[email protected]> wrote in message
news:[email protected]...
> "Ragdyer" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.
>
>

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?

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> er, it is to me.
>
> Thanks for posting a correct answer.
>
>
>
> Ragdyer wrote:
> >
> > Dave,
> > Is page break and line break the same thing in code?<g>
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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 wrote:
> > > >
> > > > 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
>
> --
>
> 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?)



Ragdyer wrote:
>
> 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?
>
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > er, it is to me.
> >
> > Thanks for posting a correct answer.
> >
> >
> >
> > Ragdyer wrote:
> > >
> > > Dave,
> > > Is page break and line break the same thing in code?<g>
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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 wrote:
> > > > >
> > > > > 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
> >
> > --
> >
> > Dave Peterson

--

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>.



Pauba wrote:
>
> "Ragdyer" <[email protected]> wrote in message
> news:%[email protected]...
> > 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.

--

Dave Peterson

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

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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?)
>
>
>
> Ragdyer wrote:
> >
> > 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?
> >
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Dave Peterson" <[email protected]> wrote in message
> > news:[email protected]...
> > > er, it is to me.
> > >
> > > Thanks for posting a correct answer.
> > >
> > >
> > >
> > > Ragdyer wrote:
> > > >
> > > > Dave,
> > > > Is page break and line break the same thing in code?<g>
> > > > --
> > > > Regards,
> > > >
> > > > RD
> > > >
> > >
> >
> --------------------------------------------------------------------------
> > -
> > > > Please keep all correspondence within the NewsGroup, so all may
benefit
> > !
> > >
> >
> --------------------------------------------------------------------------
> > -
> > > > "Dave Peterson" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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 wrote:
> > > > > >
> > > > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> 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.

Ragdyer wrote:
>
> NO !<g>
> I was talking about the Num keypad as opposed to the numbers under the
> function keys producing the CHAR() characters.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Dave Peterson" <[email protected]> wrote in message
> news:[email protected]...
> > 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?)
> >
> >
> >
> > Ragdyer wrote:
> > >
> > > 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?
> > >
> > > --
> > > Regards,
> > >
> > > RD
> > >
> >
> > --------------------------------------------------------------------------
> -
> > > Please keep all correspondence within the NewsGroup, so all may benefit
> !
> >
> > --------------------------------------------------------------------------
> -
> > > "Dave Peterson" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > er, it is to me.
> > > >
> > > > Thanks for posting a correct answer.
> > > >
> > > >
> > > >
> > > > Ragdyer wrote:
> > > > >
> > > > > Dave,
> > > > > Is page break and line break the same thing in code?<g>
> > > > > --
> > > > > Regards,
> > > > >
> > > > > RD
> > > > >
> > > >
> > >
> > --------------------------------------------------------------------------
> > > -
> > > > > Please keep all correspondence within the NewsGroup, so all may
> benefit
> > > !
> > > >
> > >
> > --------------------------------------------------------------------------
> > > -
> > > > > "Dave Peterson" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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 wrote:
> > > > > > >
> > > > > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson