Consulting

Results 1 to 16 of 16

Thread: Help with finding and highlighting Numbers in word

  1. #1

    Help with finding and highlighting Numbers in word

    Hello All,

    I am a new member and is pretty much self-taught on how to create Macros for word and excel. I need help with the following problem I having with the macro below. I created this macro to FIND AND HIGHLIGHT DOLLAR CURRENCY AMOUNT in qualitative data text for work. However, when I run the macro, it only highlights "$" and not numbers following the $ sign. I tried to use ("<[$0-9,.]{1,}>") in place of $ but no luck. Can anyone help me below. Thank in advance!

    Sub FindMoney ()

    Dim range As range
    Dim i As Long
    Dim TargetList

    TargetList = Array("$") or ("<[$0-9,.]{1,}>")
    'put list of terms to find here'

    For i = 0 To Ubound (TargetList)
    Set range = ActiveDocument.range
    With range.Find
    .Text = TargetList (i)
    .Format = True
    .MatchCase = True
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False

    Do While .Execute(Forward:=True) = True
    range.HighlightColorIndex =wdGreen
    Loop
    End With
    Next

    End Sub

  2. #2
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,845
    Location
    Sub FindMoney()
    Dim oRng As range
      Set oRng = ActiveDocument.range
      With oRng.Find
        .Text = "$([0-9.,]{1,})"
        .Format = True
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = True
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        Do While .Execute(Forward:=True) = True
          oRng.HighlightColorIndex = wdGreen
          oRng.Collapse wdCollapseEnd
        Loop
      End With
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  3. #3
    Thank you!

  4. #4
    Hi Greg, thank you so much!

    I have another question- if I wanted to find and highlight dates with various format e.g. MM/DD/YEAR MMDDYEAR YEARMMDD, are you able to modify the below script to look for the dates?


    Quote Originally Posted by gmaxey View Post
    Sub FindMoney()
    Dim oRng As range
      Set oRng = ActiveDocument.range
      With oRng.Find
        .Text = "$([0-9.,]{1,})"
        .Format = True
        .MatchCase = True
        .MatchWholeWord = False
        .MatchWildcards = True
        .MatchSoundsLike = False
        .MatchAllWordForms = False
        Do While .Execute(Forward:=True) = True
          oRng.HighlightColorIndex = wdGreen
          oRng.Collapse wdCollapseEnd
        Loop
      End With
    lbl_Exit:
      Exit Sub
    End Sub

  5. #5
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,845
    Location
    Yes, but MMDDYYYY is not a valid date format.

    Sub FindMoney()
    Dim oRng As Range
    Dim arrFind() As String
    Dim lngIndex As Long
      arrFind = Split("$[0-9.,]{1,}|[0-9]{2}/[0-9]{2}/[0-9]{4}|[0-9]{8}", "|")
      For lngIndex = 0 To UBound(arrFind)
      
        Set oRng = ActiveDocument.Range
        With oRng.Find
          .Text = arrFind(lngIndex)
          .Format = True
          .MatchCase = True
          .MatchWholeWord = False
          .MatchWildcards = True
          .MatchSoundsLike = False
          .MatchAllWordForms = False
          Do While .Execute(Forward:=True) = True
            Select Case lngIndex
              Case 0: oRng.HighlightColorIndex = wdGreen
              Case Else:
                'If IsDate(oRng.Text) Then
                  oRng.HighlightColorIndex = wdGreen
                'End If
            End Select
            oRng.Collapse wdCollapseEnd
          Loop
        End With
      Next lngIndex
    lbl_Exit:
      Exit Sub
    End Sub
    Greg

    Visit my website: http://gregmaxey.com

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,607
    Location
    Do note that the wildcard Find expression [0-9]{8} will find any 8-digit sequence in numeric strings of 8 or more digits. To reduce the likelihood of false matches, you might change that to:
    <[0-1][0-9][0-3][0-9][1-2][0,9][0-9]{2}>
    Cheers
    Paul Edstein
    [MS MVP - Word]

  7. #7
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,845
    Location
    Thanks Paul.
    Greg

    Visit my website: http://gregmaxey.com

  8. #8
    Quote Originally Posted by macropod View Post
    Do note that the wildcard Find expression [0-9]{8} will find any 8-digit sequence in numeric strings of 8 or more digits. To reduce the likelihood of false matches, you might change that to:
    <[0-1][0-9][0-3][0-9][1-2][0,9][0-9]{2}>

    Hi Paul, thank you for you input. I apologize for sounding green but where should I insert the above expression? Should I replace just the [0-9]{8} to the above listed one?

  9. #9
    Quote Originally Posted by gmaxey View Post
    Yes, but MMDDYYYY is not a valid date format.

    Sub FindMoney()
    Dim oRng As Range
    Dim arrFind() As String
    Dim lngIndex As Long
      arrFind = Split("$[0-9.,]{1,}|[0-9]{2}/[0-9]{2}/[0-9]{4}|[0-9]{8}", "|")
      For lngIndex = 0 To UBound(arrFind)
      
        Set oRng = ActiveDocument.Range
        With oRng.Find
          .Text = arrFind(lngIndex)
          .Format = True
          .MatchCase = True
          .MatchWholeWord = False
          .MatchWildcards = True
          .MatchSoundsLike = False
          .MatchAllWordForms = False
          Do While .Execute(Forward:=True) = True
            Select Case lngIndex
              Case 0: oRng.HighlightColorIndex = wdGreen
              Case Else:
                'If IsDate(oRng.Text) Then
                  oRng.HighlightColorIndex = wdGreen
                'End If
            End Select
            oRng.Collapse wdCollapseEnd
          Loop
        End With
      Next lngIndex
    lbl_Exit:
      Exit Sub
    End Sub
    I ran the code and it seems to be overlooking quite a few dates. Its picking up some but missing others. Do you know why this is?

  10. #10
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,607
    Location
    Quote Originally Posted by Macro_king35 View Post
    Should I replace just the [0-9]{8} to the above listed one?
    That's sufficient for capturing the MMDDYYYY dates, but you'll also want to add:
    |<[1-2][0,9][0-9]{2}[0-1][0-9][0-3][0-9]>
    for capturing the YYYYMMDD dates. Hence:
    arrFind = Split("$[0-9.,]{1,}|[0-9]{2}/[0-9]{2}/[0-9]{4}|[0-9]{8}", "|")
    becomes:
    arrFind = Split("$[0-9.,]{1,}|[0-9]{2}/[0-9]{2}/[0-9]{4}|<[0-1][0-9][0-3][0-9][1-2][0,9][0-9]{2}>|<[1-2][0,9][0-9]{2}[0-1][0-9][0-3][0-9]>", "|")
    Cheers
    Paul Edstein
    [MS MVP - Word]

  11. #11
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,607
    Location
    Quote Originally Posted by Macro_king35 View Post
    I ran the code and it seems to be overlooking quite a few dates. Its picking up some but missing others. Do you know why this is?
    Unless you can tell us what date formats it's missing, we can't really help.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  12. #12
    Quote Originally Posted by macropod View Post
    Unless you can tell us what date formats it's missing, we can't really help.

    it's overlooking dates formatted such as 12/01/2017 for some and capturing others. Also, date formatted such as 11/1/2017 or 11/1/17.

  13. #13
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,607
    Location
    Well, you did specify the dates as MM/DD/YEAR, which would exclude M/D/YEAR where either the month or the day has only a single digit. As for your YEAR specification, that is quite ambiguous. Years are normally specified as YYYY or YY - one could only guess what YEAR meant. Change:
    [0-9]{2}/[0-9]{2}/[0-9]{4}
    to:
    [0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}
    I can't see how it's possible for the existing code to miss dates like 12/01/2017, since that format is explicitly provided for.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  14. #14
    Thank you Paul and Greg. I'm sure I will have more questions in the future.
    How do I learn VBA coding to get as good as you guys? Is there a course or method you recommend?

  15. #15
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    3,607
    Location
    Time and practice. All the VBA I know is essentially self-taught (mostly for the purpose of helping others find solutions through forums such as this), though I've studied the code of others (including Greg's) along the way, too. In this case, not only was macro coding required, but so too was an understanding of how to use Find/Replace wildcards.
    Cheers
    Paul Edstein
    [MS MVP - Word]

  16. #16
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    2,845
    Location
    Actually I'm not that good. Supposedly a Boeing 757 pilot was once asked how he every learned to flight such an massive airplane. His response, "You just have so sit in the cockpit and operate the controls." Like Paul, mostly self taught. At some point I started answering questions in forums like this more often than I was asking them (I still ask). I did read and have a heavily dog eared copy of "RibbonX Customizing the Office 2007 Ribbon" which has been helpful. Don't feel bad. I don't understand find and replace that well either. Don't have to as long as Paul is around.
    Greg

    Visit my website: http://gregmaxey.com

Posting Permissions

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