On Nov 18, 10:37*am, guitarpants
wrote:
I'm trying to find a fast way to search through financial statement footnotes
and find instances where monetary amounts are not rounded to nearest dollar.
For instance, where $151,252.11 is typed, there should only be $151,252 (no
pennies).
Is there a way to search for monetary amounts that end in pennies?
Pesach has shown you how to find the values. If you want to automate
the process, the following macro will find each instance and round the
value up or down (=.50 rounds up):
Public Sub FindAndRoundMonetaryValues()
Dim rngStory As Word.Range
Dim pFindTxt As String
Dim pReplaceTxt As String
Dim lngJunk As Long
Dim oShp As Shape
pFindTxt = "$[0-9,]{1,}.[0-9]{2}"
'Fix the skipped blank Header/Footer problem
lngJunk = ActiveDocument.Sections(1).Headers(1).Range.StoryT ype
'Iterate through all story types in the current document
For Each rngStory In ActiveDocument.StoryRanges
'Iterate through all linked stories
Do
SrcAndRplInSty rngStory, pFindTxt
On Error Resume Next
Select Case rngStory.StoryType
Case 6, 7, 8, 9, 10, 11
If rngStory.ShapeRange.Count 0 Then
For Each oShp In rngStory.ShapeRange
If oShp.TextFrame.HasText Then
SrcAndRplInSty oShp.TextFrame.TextRange, pFindTxt
End If
Next
End If
Case Else
'Do Nothing
End Select
On Error GoTo 0
'Get next linked story (if any)
Set rngStory = rngStory.NextStoryRange
Loop Until rngStory Is Nothing
Next
End Sub
Public Sub SrcAndRplInSty(ByVal rngStory As Word.Range, _
ByVal strSearch As String)
With rngStory.Find
.Text = strSearch
.MatchWildcards = True
While .Execute
rngStory.Text = "$" & Format(rngStory.Text, "#,000")
rngStory.Collapse Direction:=wdCollapseEnd
Wend
End With
End Sub
For help installing and using the macro see:
http://www.gmayor.com/installing_macro.htm