Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Location
    Brentwood, CA (not the OJ one)
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Find and replace text with next month in sequence

    Hello all,

    This is my first post to any Word/Excel help forums. I am a beginner with VBA, and have been muddling through all my code, so far with mostly success. However, my latest problem has me stumped. I have Googled all day, and have come up with the following code. I have a Word 2010 document with a table with a certain amount of rows. Every other row starting at the third row has a word in it for one of the months, e.g. January, February, etc. Upon running my macro, I want to change the month to the next month in sequence. So, if the text is March, I want to change it to April, etc.

    Here is my so-called code so far:

    Code:
    For row = 3 To ActiveDocument.Tables(1).Rows.Count Step 2
            Selection.Collapse Direction:=wdCollapseStart
            Set rng = ActiveDocument.Tables(1).Cell(row, Column:=1).Range
            rng.Select
                                 
            If Selection = "January" Then
                Selection = "February"
            ElseIf Selection = "February" Then
                Selection = "March"
            End If
        Next
    I put a watch on rng and selection, and it finds the text "January", but it finds it as "January *" with the star being a dot that looks like a bullet. So, my code does not find January exactly, I guess because of the extra dot at the end, and it skips it.

    What am I doing wrong, please? Is there a way to upload my word document, or is that not possible or helpful?

    Thanks!
    -=Jeff

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jeff,

    Welcome to the Lounge as a New Poster!

    If I understand your requirements correctly this code should do the trick.
    Code:
    Option Explicit
    
    Sub AdvanceMonth()
    
       Dim Row     As Long
       Dim Months  As Variant
       Dim CurMon  As Integer
       Dim Rng     As Range
       Dim TestVal As String
       
       Months = Array("January", "February", "March", "April", "May", "June", _
                "July", "August", "September", "October", "November", "December", _
                "January")
       
    
        For Row = 3 To ActiveDocument.Tables(1).Rows.Count Step 2
            Selection.Collapse Direction:=wdCollapseStart
            Set Rng = ActiveDocument.Tables(1).Cell(Row, Column:=1).Range
            Rng.Select
                                 
            TestVal = UCase(Left(Selection, Selection.Characters.Count - 1))
            
            For CurMon = 0 To UBound(Months) - 1
            
               If TestVal = UCase(Months(CurMon)) Then
                 Selection = Months(CurMon + 1) & Right(Selection, 1)
                 Exit For
               End If
               
            Next CurMon
                                 
        Next Row
    
    End Sub   'AdvanceMonth()
    Test File: VBA - Word - Rolling Months in Table.docm
    HTH
    Last edited by RetiredGeek; 2014-12-30 at 20:47.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    New Lounger
    Join Date
    Dec 2014
    Location
    Brentwood, CA (not the OJ one)
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, my friend! This works perfectly. I REALLY appreciate the help.

    Is there a good VBA book you would recommend for a beginner to get started really learning Word and Excel VBA?

    Thanks!
    -=Jeff

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jeff,

    I have quite a library of VBA books as each one has something a little different and I figured (when I was working) that book that saved me even one hour of time had paid for itself.

    Here are some of my Favorites:

    Excel yyyy Power Programming with VBA: Walkenback (yyyy = 2000, 2003, 2010, 2013 pick your version although they are mostly the same material he adds new stuff as the capabilities change).

    Professional Excel Development: Bullen, Bovey, Green

    Visual Basic Programmer's Guide to the Win32 API: Dan Appleman (Advanced)

    VB & VBA In A Nutshell: Lomax (Reference)

    Writing Word Macros: Steven Roman
    Writing Excel Macros: Steven Roman

    There are of course many others. One thing to remember is that the VBA Control Language is the SAME for all, so there is a lot of repetition, however, the Object Models are of course different for each product and how to interface with them will naturally be different and is why you need more than one book.

    Don't forget that Google is your friend as well as all us here at WSL!

    HTH - Happy New Year
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    New Lounger
    Join Date
    Dec 2014
    Location
    Brentwood, CA (not the OJ one)
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Great list of books. That should get me started! Last question, then I will leave you alone (for now).

    What does the & Right(Selection, 1) do in the statement Selection = Months(CurMon + 1) & Right(Selection, 1)? Why is that necessary?

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Jeff,

    That copies that weird character that is at the end of the month (I think it is the cell terminator) you referenced it as a Bullet in your OP. I stripped it off (see "TestVal =" line) for the comparison then add it back in here so the replacement value will have the same format as the original value. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Building on RG's excellent work, here is a shorter alternative that does the same thing
    Code:
    Sub AdvanceMonth2()
      Dim Row As Long
      Dim Rng As Range
    
      For Row = 3 To ActiveDocument.Tables(1).Rows.Count Step 2
        Set Rng = ActiveDocument.Tables(1).Cell(Row, Column:=1).Range
        Rng.Words(1) = NextMonth(Rng.Words(1))
      Next Row
    
    End Sub 'AdvanceMonth2()
    Function NextMonth(m As String) As String
      NextMonth = Format(DateAdd("m", 1, DateValue("1 " & m & " 2000")), "mmmm")
    End Function
    This code was adapted from this thread I googled http://stackoverflow.com/questions/1...-of-next-month
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. The Following User Says Thank You to Andrew Lockton For This Useful Post:

    RetiredGeek (2014-12-31)

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    Very Cool!

    Happy New Year!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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