+ Reply to Thread
Results 1 to 34 of 34

Need Macro for List of Book Titles

Hybrid View

  1. #1
    Bob Phillips
    Guest

    Re: Need Macro for List of Book Titles

    Wrap-around Edye. Try this

    Sub BookTitles()
    Dim iLastRow As Long
    Dim i As Long

    iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For i = 1 To iLastRow
    If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    Len(Cells(i, "A").Value) - 4) & ", The"
    End If
    Next i

    End Sub


    --

    HTH

    RP
    (remove nothere from the email address if mailing direct)


    "Edye" <Edye@discussions.microsoft.com> wrote in message
    news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > I get Comple Error: Sub or Function not defined. It highlights the last
    > "the" (w/o quotes).
    >
    > "Bob Phillips" wrote:
    >
    > > Sub BookTitles()
    > > Dim iLastRow As Long
    > > Dim i As Long
    > >
    > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > For i = 1 To iLastRow
    > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > Len(Cells(i, "A").Value) - 4) &

    ",
    > > The"
    > > End If
    > > Next i
    > >
    > > End Sub
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > One more little thing I noticed. There's a space b/w the end of the

    title
    > > > and the ", The". How do I edit the macro to remove that space?
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > >
    > > > > Sub BookTitles()
    > > > > Dim iLastRow As Long
    > > > > Dim i As Long
    > > > >
    > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > For i = 1 To iLastRow
    > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value, Len( _
    > > > > Cells(i, "A").Value) - 4) &

    ",
    > > The"
    > > > > End If
    > > > > Next i
    > > > >
    > > > > End Sub
    > > > >
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > Excel 2003
    > > > > > I have a list of books. About 700 contain a "The" at the

    beginning.
    > > I
    > > > > need
    > > > > > to add ", The" (without quotes) to the end of each one of those

    book
    > > > > titles
    > > > > > and remove the "The" at the beginning of the title. I recorded a

    > > Macro
    > > > > for
    > > > > > it, but each time I use the Macro, it replaces the other cells

    with
    > > the
    > > > > title
    > > > > > of the one which I recorded. Thanks!
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  2. #2
    Edye
    Guest

    Re: Need Macro for List of Book Titles

    Perfect! Thanks for your time. A few minutes of yours saved me many hours
    of mine. You're the greatest!

    "Bob Phillips" wrote:

    > Wrap-around Edye. Try this
    >
    > Sub BookTitles()
    > Dim iLastRow As Long
    > Dim i As Long
    >
    > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > For i = 1 To iLastRow
    > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > Len(Cells(i, "A").Value) - 4) & ", The"
    > End If
    > Next i
    >
    > End Sub
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > I get Comple Error: Sub or Function not defined. It highlights the last
    > > "the" (w/o quotes).
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Sub BookTitles()
    > > > Dim iLastRow As Long
    > > > Dim i As Long
    > > >
    > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > For i = 1 To iLastRow
    > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > Len(Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > End If
    > > > Next i
    > > >
    > > > End Sub
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > One more little thing I noticed. There's a space b/w the end of the

    > title
    > > > > and the ", The". How do I edit the macro to remove that space?
    > > > >
    > > > > "Bob Phillips" wrote:
    > > > >
    > > > > >
    > > > > > Sub BookTitles()
    > > > > > Dim iLastRow As Long
    > > > > > Dim i As Long
    > > > > >
    > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > For i = 1 To iLastRow
    > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value, Len( _
    > > > > > Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > > > End If
    > > > > > Next i
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > HTH
    > > > > >
    > > > > > RP
    > > > > > (remove nothere from the email address if mailing direct)
    > > > > >
    > > > > >
    > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > Excel 2003
    > > > > > > I have a list of books. About 700 contain a "The" at the

    > beginning.
    > > > I
    > > > > > need
    > > > > > > to add ", The" (without quotes) to the end of each one of those

    > book
    > > > > > titles
    > > > > > > and remove the "The" at the beginning of the title. I recorded a
    > > > Macro
    > > > > > for
    > > > > > > it, but each time I use the Macro, it replaces the other cells

    > with
    > > > the
    > > > > > title
    > > > > > > of the one which I recorded. Thanks!
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  3. #3
    Edye
    Guest

    Re: Need Macro for List of Book Titles

    Perfect! Thanks for your time. A few minutes of yours saved me many hours
    of mine. You're the greatest!

    "Bob Phillips" wrote:

    > Wrap-around Edye. Try this
    >
    > Sub BookTitles()
    > Dim iLastRow As Long
    > Dim i As Long
    >
    > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > For i = 1 To iLastRow
    > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > Len(Cells(i, "A").Value) - 4) & ", The"
    > End If
    > Next i
    >
    > End Sub
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > I get Comple Error: Sub or Function not defined. It highlights the last
    > > "the" (w/o quotes).
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Sub BookTitles()
    > > > Dim iLastRow As Long
    > > > Dim i As Long
    > > >
    > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > For i = 1 To iLastRow
    > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > Len(Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > End If
    > > > Next i
    > > >
    > > > End Sub
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > One more little thing I noticed. There's a space b/w the end of the

    > title
    > > > > and the ", The". How do I edit the macro to remove that space?
    > > > >
    > > > > "Bob Phillips" wrote:
    > > > >
    > > > > >
    > > > > > Sub BookTitles()
    > > > > > Dim iLastRow As Long
    > > > > > Dim i As Long
    > > > > >
    > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > For i = 1 To iLastRow
    > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value, Len( _
    > > > > > Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > > > End If
    > > > > > Next i
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > HTH
    > > > > >
    > > > > > RP
    > > > > > (remove nothere from the email address if mailing direct)
    > > > > >
    > > > > >
    > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > Excel 2003
    > > > > > > I have a list of books. About 700 contain a "The" at the

    > beginning.
    > > > I
    > > > > > need
    > > > > > > to add ", The" (without quotes) to the end of each one of those

    > book
    > > > > > titles
    > > > > > > and remove the "The" at the beginning of the title. I recorded a
    > > > Macro
    > > > > > for
    > > > > > > it, but each time I use the Macro, it replaces the other cells

    > with
    > > > the
    > > > > > title
    > > > > > > of the one which I recorded. Thanks!
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  4. #4
    Edye
    Guest

    Re: Need Macro for List of Book Titles

    Perfect! Thanks for your time. A few minutes of yours saved me many hours
    of mine. You're the greatest!

    "Bob Phillips" wrote:

    > Wrap-around Edye. Try this
    >
    > Sub BookTitles()
    > Dim iLastRow As Long
    > Dim i As Long
    >
    > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > For i = 1 To iLastRow
    > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > Len(Cells(i, "A").Value) - 4) & ", The"
    > End If
    > Next i
    >
    > End Sub
    >
    >
    > --
    >
    > HTH
    >
    > RP
    > (remove nothere from the email address if mailing direct)
    >
    >
    > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > I get Comple Error: Sub or Function not defined. It highlights the last
    > > "the" (w/o quotes).
    > >
    > > "Bob Phillips" wrote:
    > >
    > > > Sub BookTitles()
    > > > Dim iLastRow As Long
    > > > Dim i As Long
    > > >
    > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > For i = 1 To iLastRow
    > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > Len(Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > End If
    > > > Next i
    > > >
    > > > End Sub
    > > >
    > > > --
    > > >
    > > > HTH
    > > >
    > > > RP
    > > > (remove nothere from the email address if mailing direct)
    > > >
    > > >
    > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > One more little thing I noticed. There's a space b/w the end of the

    > title
    > > > > and the ", The". How do I edit the macro to remove that space?
    > > > >
    > > > > "Bob Phillips" wrote:
    > > > >
    > > > > >
    > > > > > Sub BookTitles()
    > > > > > Dim iLastRow As Long
    > > > > > Dim i As Long
    > > > > >
    > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > For i = 1 To iLastRow
    > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value, Len( _
    > > > > > Cells(i, "A").Value) - 4) &

    > ",
    > > > The"
    > > > > > End If
    > > > > > Next i
    > > > > >
    > > > > > End Sub
    > > > > >
    > > > > >
    > > > > > --
    > > > > >
    > > > > > HTH
    > > > > >
    > > > > > RP
    > > > > > (remove nothere from the email address if mailing direct)
    > > > > >
    > > > > >
    > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > Excel 2003
    > > > > > > I have a list of books. About 700 contain a "The" at the

    > beginning.
    > > > I
    > > > > > need
    > > > > > > to add ", The" (without quotes) to the end of each one of those

    > book
    > > > > > titles
    > > > > > > and remove the "The" at the beginning of the title. I recorded a
    > > > Macro
    > > > > > for
    > > > > > > it, but each time I use the Macro, it replaces the other cells

    > with
    > > > the
    > > > > > title
    > > > > > > of the one which I recorded. Thanks!
    > > > > >
    > > > > >
    > > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: Need Macro for List of Book Titles

    Glad it worked out okay.

    Regards

    Bob


    "Edye" <Edye@discussions.microsoft.com> wrote in message
    news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
    > Perfect! Thanks for your time. A few minutes of yours saved me many

    hours
    > of mine. You're the greatest!
    >
    > "Bob Phillips" wrote:
    >
    > > Wrap-around Edye. Try this
    > >
    > > Sub BookTitles()
    > > Dim iLastRow As Long
    > > Dim i As Long
    > >
    > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > For i = 1 To iLastRow
    > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > Len(Cells(i, "A").Value) - 4) & ", The"
    > > End If
    > > Next i
    > >
    > > End Sub
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > > I get Comple Error: Sub or Function not defined. It highlights the

    last
    > > > "the" (w/o quotes).
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Sub BookTitles()
    > > > > Dim iLastRow As Long
    > > > > Dim i As Long
    > > > >
    > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > For i = 1 To iLastRow
    > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > > Len(Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > End If
    > > > > Next i
    > > > >
    > > > > End Sub
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > > One more little thing I noticed. There's a space b/w the end of t

    he
    > > title
    > > > > > and the ", The". How do I edit the macro to remove that space?
    > > > > >
    > > > > > "Bob Phillips" wrote:
    > > > > >
    > > > > > >
    > > > > > > Sub BookTitles()
    > > > > > > Dim iLastRow As Long
    > > > > > > Dim i As Long
    > > > > > >
    > > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > > For i = 1 To iLastRow
    > > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value,

    Len( _
    > > > > > > Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > > > End If
    > > > > > > Next i
    > > > > > >
    > > > > > > End Sub
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > HTH
    > > > > > >
    > > > > > > RP
    > > > > > > (remove nothere from the email address if mailing direct)
    > > > > > >
    > > > > > >
    > > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > > Excel 2003
    > > > > > > > I have a list of books. About 700 contain a "The" at the

    > > beginning.
    > > > > I
    > > > > > > need
    > > > > > > > to add ", The" (without quotes) to the end of each one of

    those
    > > book
    > > > > > > titles
    > > > > > > > and remove the "The" at the beginning of the title. I

    recorded a
    > > > > Macro
    > > > > > > for
    > > > > > > > it, but each time I use the Macro, it replaces the other cells

    > > with
    > > > > the
    > > > > > > title
    > > > > > > > of the one which I recorded. Thanks!
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  6. #6
    Bob Phillips
    Guest

    Re: Need Macro for List of Book Titles

    Glad it worked out okay.

    Regards

    Bob


    "Edye" <Edye@discussions.microsoft.com> wrote in message
    news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
    > Perfect! Thanks for your time. A few minutes of yours saved me many

    hours
    > of mine. You're the greatest!
    >
    > "Bob Phillips" wrote:
    >
    > > Wrap-around Edye. Try this
    > >
    > > Sub BookTitles()
    > > Dim iLastRow As Long
    > > Dim i As Long
    > >
    > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > For i = 1 To iLastRow
    > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > Len(Cells(i, "A").Value) - 4) & ", The"
    > > End If
    > > Next i
    > >
    > > End Sub
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > > I get Comple Error: Sub or Function not defined. It highlights the

    last
    > > > "the" (w/o quotes).
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Sub BookTitles()
    > > > > Dim iLastRow As Long
    > > > > Dim i As Long
    > > > >
    > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > For i = 1 To iLastRow
    > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > > Len(Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > End If
    > > > > Next i
    > > > >
    > > > > End Sub
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > > One more little thing I noticed. There's a space b/w the end of t

    he
    > > title
    > > > > > and the ", The". How do I edit the macro to remove that space?
    > > > > >
    > > > > > "Bob Phillips" wrote:
    > > > > >
    > > > > > >
    > > > > > > Sub BookTitles()
    > > > > > > Dim iLastRow As Long
    > > > > > > Dim i As Long
    > > > > > >
    > > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > > For i = 1 To iLastRow
    > > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value,

    Len( _
    > > > > > > Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > > > End If
    > > > > > > Next i
    > > > > > >
    > > > > > > End Sub
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > HTH
    > > > > > >
    > > > > > > RP
    > > > > > > (remove nothere from the email address if mailing direct)
    > > > > > >
    > > > > > >
    > > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > > Excel 2003
    > > > > > > > I have a list of books. About 700 contain a "The" at the

    > > beginning.
    > > > > I
    > > > > > > need
    > > > > > > > to add ", The" (without quotes) to the end of each one of

    those
    > > book
    > > > > > > titles
    > > > > > > > and remove the "The" at the beginning of the title. I

    recorded a
    > > > > Macro
    > > > > > > for
    > > > > > > > it, but each time I use the Macro, it replaces the other cells

    > > with
    > > > > the
    > > > > > > title
    > > > > > > > of the one which I recorded. Thanks!
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




  7. #7
    Bob Phillips
    Guest

    Re: Need Macro for List of Book Titles

    Glad it worked out okay.

    Regards

    Bob


    "Edye" <Edye@discussions.microsoft.com> wrote in message
    news:41C7D28D-FB6E-4536-9053-F3FB8DE16DED@microsoft.com...
    > Perfect! Thanks for your time. A few minutes of yours saved me many

    hours
    > of mine. You're the greatest!
    >
    > "Bob Phillips" wrote:
    >
    > > Wrap-around Edye. Try this
    > >
    > > Sub BookTitles()
    > > Dim iLastRow As Long
    > > Dim i As Long
    > >
    > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > For i = 1 To iLastRow
    > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > Len(Cells(i, "A").Value) - 4) & ", The"
    > > End If
    > > Next i
    > >
    > > End Sub
    > >
    > >
    > > --
    > >
    > > HTH
    > >
    > > RP
    > > (remove nothere from the email address if mailing direct)
    > >
    > >
    > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > news:50D074FB-8499-4269-8574-CAEBA8C75299@microsoft.com...
    > > > I get Comple Error: Sub or Function not defined. It highlights the

    last
    > > > "the" (w/o quotes).
    > > >
    > > > "Bob Phillips" wrote:
    > > >
    > > > > Sub BookTitles()
    > > > > Dim iLastRow As Long
    > > > > Dim i As Long
    > > > >
    > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > For i = 1 To iLastRow
    > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > Cells(i, "A").Value = Right(Trim(Cells(i, "A").Value), _
    > > > > Len(Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > End If
    > > > > Next i
    > > > >
    > > > > End Sub
    > > > >
    > > > > --
    > > > >
    > > > > HTH
    > > > >
    > > > > RP
    > > > > (remove nothere from the email address if mailing direct)
    > > > >
    > > > >
    > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > news:2D874923-5268-46CE-A145-CC3FF6C92473@microsoft.com...
    > > > > > One more little thing I noticed. There's a space b/w the end of t

    he
    > > title
    > > > > > and the ", The". How do I edit the macro to remove that space?
    > > > > >
    > > > > > "Bob Phillips" wrote:
    > > > > >
    > > > > > >
    > > > > > > Sub BookTitles()
    > > > > > > Dim iLastRow As Long
    > > > > > > Dim i As Long
    > > > > > >
    > > > > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
    > > > > > > For i = 1 To iLastRow
    > > > > > > If LCase(Left(Cells(i, "A").Value, 4)) = "the " Then
    > > > > > > Cells(i, "A").Value = Right(Cells(i, "A").Value,

    Len( _
    > > > > > > Cells(i, "A").Value) -

    4) &
    > > ",
    > > > > The"
    > > > > > > End If
    > > > > > > Next i
    > > > > > >
    > > > > > > End Sub
    > > > > > >
    > > > > > >
    > > > > > > --
    > > > > > >
    > > > > > > HTH
    > > > > > >
    > > > > > > RP
    > > > > > > (remove nothere from the email address if mailing direct)
    > > > > > >
    > > > > > >
    > > > > > > "Edye" <Edye@discussions.microsoft.com> wrote in message
    > > > > > > news:85DF4268-CB44-495E-9830-AD7FC82BE4C1@microsoft.com...
    > > > > > > > Excel 2003
    > > > > > > > I have a list of books. About 700 contain a "The" at the

    > > beginning.
    > > > > I
    > > > > > > need
    > > > > > > > to add ", The" (without quotes) to the end of each one of

    those
    > > book
    > > > > > > titles
    > > > > > > > and remove the "The" at the beginning of the title. I

    recorded a
    > > > > Macro
    > > > > > > for
    > > > > > > > it, but each time I use the Macro, it replaces the other cells

    > > with
    > > > > the
    > > > > > > title
    > > > > > > > of the one which I recorded. Thanks!
    > > > > > >
    > > > > > >
    > > > > > >
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1