Bookmark Formatting for Currency - HELP!!!!

[SpeedyFingers]

Please I NEED help!!! My cells are formatted for currency but don't push through on the word document. Any suggestions????

Sub Create_Letters()'
Dim objX As Object
Dim rng1 As Range
Dim rng2 As Range
Dim wb As Workbook
Dim wsControl As Worksheet
Dim wsData As Worksheet
'
Dim oApp As Word.Application
Dim oBookMark As Word.Bookmark
Dim oDoc As Word.Document
'
Dim strDocumentFolder As String
Dim strTemplate As String
Dim strTemplateFolder As String
Dim lngTemplateNameColumn As Long
Dim strWordDocumentName As String
Dim lngDocumentNameColumn As Long
Dim lngRecordKount As Long ' not used but retain for future use
'
Set wb = ThisWorkbook
Set wsControl = wb.Worksheets("Control Sheet")
wsControl.Activate
Set wsData = wb.Worksheets(wsControl.[Data_Sheet].Value)
strTemplateFolder = wsControl.[Template_Folder].Value
strDocumentFolder = wsControl.[Document_Folder].Value
wsData.Activate
lngTemplateNameColumn = wsData.[Template_Name].Column
lngDocumentNameColumn = wsData.[Document_Name].Column
'number of letters required:
'must not have any blank cells in column A - except at the end
Set rng1 = wsData.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp))
lngRecordKount = rng1.Rows.Count
'
'Set oApp = CreateObject("Word Application")
Set oApp = New Word.Application
'Process each record in turn
For Each rng2 In rng1
    strTemplate = strTemplateFolder & "\" & wsData.Cells(rng2.Row, lngTemplateNameColumn)
    strWordDocumentName = strDocumentFolder & "\" & wsData.Cells(rng2.Row, lngDocumentNameColumn)
'check that template exists
    If Dir(strTemplate) = "" Then
        MsgBox strTemplate & "not found"
        GoTo Tidy_Exit
    End If
    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 oBookMark.Name = "ClosingCost" Then
            oDoc.Bookmarks("ClosingCost").Select
            oBookMark.Range.Text = Format(oBookMark.Name, "$#,##0.00")
        If Not objX Is Nothing Then
        
            ' found
'            ElseIf Right(oBookMark.Name, 4) = "ClosingCost" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "ClosingCost")
'            ElseIf Right(oBookMark.Name, 2) = "Loan" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "Loan")
'            ElseIf Right(oBookMark.Name, 6) = "CustomerName2" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "CustomerName2")
'            ElseIf Right(oBookMark.Name, 8) = "MailingAddressCity" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "MailingAddressCity")
'            ElseIf Right(oBookMark.Name, 9) = "MailingAddressState" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "MailingAddressState")
'            ElseIf Right(oBookMark.Name, 10) = "MailingAddressZip" Then
'                oBookMark.Range.Text = Format(wsData.Cells(rng2.Row, objX.Column), "MailingAddressZip")
'            Else


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


Tidy_Exit:
On Error Resume Next
Set oDoc = Nothing
Set oBookMark = Nothing
Set objX = Nothing
Set rng1 = Nothing
Set rng2 = Nothing
oApp.Quit
Set oApp = Nothing
Set Name = Nothing
'


Set wsData = Nothing
Set wsControl = Nothing
Set wb = Nothing
'


Sheets("Cover").Activate
Range("A1").Select


End Sub

 

[Derek Brown]

You should be formatting the data being merged and not the bookmark itself:

'oDoc.Bookmarks("ClosingCost").Select
oBookMark.Range.Text = Format(*add the cell reference where the data is*, "$#,##0.00")

For examples, look at the commented lines in the code that you posted.