Using If Formula within VB Code in Excel

[Gordo24]

Not sure if this is possible, but I'm sure somebody a lot better than I am will be able to confirm. I have created a macro in my Excel source document which opens a Word template and populates my bookmarks within Word with Excel data. One of my data cells is a drop down list. My drop down list options are:

Annual Payments
Quarterly Payments
Monthly Payments

When my bookmark is filled, I need it to fill as follows:

year if Annual Payments is selected
quarter if Quarterly Payments is selected
month if Monthly Payments is selected

Basically I'm wondering if there is a way to use an IF formula in my excel macro so if my excel cell has "Annual Payments" it populates my Word bookmark as "year." Changing my Excel drop down list is not an option. Here is my code stripped down so you get an idea of what I'm doing:

Sub createTemplate()
On Error GoTo errorHandler
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim mywdRange As Word.Range
Dim Payout_Frequency As Excel.Range

Set wdApp = New Word.Application
With wdApp
.Visible = True
.WindowState = wdWindowStateMaximize
End With
Set myDoc = wdApp.Documents.Add(Template:="C:\Test.doc")

Set Payout_Frequency = Sheets("Sheet1").Range("B2")


With myDoc.Bookmarks
.Item("Payout_Frequency").Range.InsertAfter Payout_Frequency.Text

End With

errorHandler:
Set wdApp = Nothing
Set myDoc = Nothing
Set mywdRange = Nothing
End Sub

 

[PJ_in_FL]

Try using Select Case to set the different bookmarks. Here's a possible solution:

Sub createTemplate()
    On Error GoTo errorHandler
    Dim wdApp As Word.Application
    Dim myDoc As Word.Document
    Dim mywdRange As Word.Range
    Dim Payout_Frequency As Excel.Range
    
    Set wdApp = New Word.Application
    With wdApp
        .Visible = True
        .WindowState = wdWindowStateMaximize
    End With
    
    Set myDoc = wdApp.Documents.Add(Template:="C:\Test.doc")
    
    Set Payout_Frequency = Sheets("Sheet1").Range("B2")
    
    Select Case Payout_Frequency.Value
    Case "Annual Payments"
        myDoc.Bookmarks.Item("Payout_Frequency").Range.InsertAfter "year"
    Case "Quarterly Payments"
        myDoc.Bookmarks.Item("Payout_Frequency").Range.InsertAfter "quarter"
    Case "Monthly Payments"
        myDoc.Bookmarks.Item("Payout_Frequency").Range.InsertAfter "month"
    Case Else
        MsgBox "Invalid selection in Payout Frequency"
    End Select
    
errorHandler:
    Set wdApp = Nothing
    Set myDoc = Nothing
    Set mywdRange = Nothing
End Sub

 

[Gordo24]

Thanks PJ in FL...worked perfectly. I removed the "case else" as there will be instances when the drop down will not be utilized and I don't want the error message poping up.