Help with finding and highlighting Numbers in word

[Macro_king35]

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

[gmaxey]

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

[Macro_king35]

Thank you!

[Macro_king35]

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?




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

[gmaxey]

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

[macropod]

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

[gmaxey]

Thanks Paul.

[Macro_king35]

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?

[Macro_king35]

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?

[macropod]

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]>", "|")

[macropod]

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.

[Macro_king35]

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.

[macropod]

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.

[Macro_king35]

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?

[macropod]

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.

[gmaxey]

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.