Bookmark Currency Formatting within VBA

[SpeedyFingers]

In need of some desperate help. Can't get the bookmark to keep the excel (Currency) format. Any help is appreciated.

    Set oDoc = oApp.Documents.Add    oApp.Selection.InsertFile strTemplate
    'locate each bookmark
    For Each oBookMark In oDoc.Bookmarks
        Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, lookat:=xlWhole)


        If Not objX Is Nothing Then
            ' found
            If Right(oBookMark.Name, 3) = "ClosingCost" Then
                oBookMark.Range.Text = Format("ClosingCost", "$#,##0.00_);($#,##0.00)")
'            ElseIf Right(oBookMark.Name, 2) = "Loan" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "Loan")
'            Else
                oBookMark.Range.Text = wsData.Cells(rng2.Row, objX.Column)
'            End If
        Else
            MsgBox "Bookmark '" & oBookMark.Name & "' not found", vbOKOnly + vbCritical, "error"
            GoTo Tidy_Exit
        End If
    Next oBookMark
    '


    oDoc.SaveAs strWordDocumentName & ".doc"
    oDoc.Close
Next rng2

 

[Derek Brown]

Duplicated Post: http://www.mrexcel.com/forum/excel-questions/681418-bookmark-formatting-currency-help.html
Please see Forum Rule #9 http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html