Building string in Excel VBA to output to Word is returning "False" instead of string

73 views Asked by At

I'm trying to build a string in Excel VBA that will then be output to a Word file.
The basic strings I'm creating are working fine, but these complex strings are coming in to Word as "False" so I'm not sure what I'm doing wrong.

I'm working with Excel / Word 2013.

Example excel file and word template here: https://dl.dropboxusercontent.com/u/5611192/Sales%20Package%20-%20Test%20for%20Word%20Proposal.xlsm

https://dl.dropboxusercontent.com/u/5611192/Proposal.docx

The strings that aren't working are built with the following loop:

With Sheet1
    For RowCnt = Firstrow To Lastrow
        If .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "" Then
            strProducts = strProducts & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And Cells(RowCnt, 13).Value = "1" Then
            strOptions1 = strOptions1 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "2" Then
            strOptions2 = strOptions2 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "3" Then
            strOptions3 = strOptions3 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "4" Then
            strOptions4 = strOptions4 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "5" Then
            strOptions5 = strOptions5 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        ElseIf .Cells(RowCnt, 15).Value = "x" And .Cells(RowCnt, 13).Value = "6" Then
            strOptions6 = strOptions6 & .Cells(RowCnt, 2).Value & " " & .Cells(RowCnt, 8).Value & Chr(11)
        End If
    Next RowCnt
End With

strProducts = strProducts & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strPrice
strOptions1 = strOptions1 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption1Price
strOptions2 = strOptions2 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption2Price
strOptions3 = strOptions3 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption3Price
strOptions4 = strOptions4 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption4Price
strOptions5 = strOptions5 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption5Price
strOptions6 = strOptions6 & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strOption6Price
1

There are 1 answers

6
AnalystCave.com On BEST ANSWER

The solution

Taking the example of the first string:

strProducts = strProducts & Chr(11) & Range("A47").Font.Bold = True & Chr(11) & "Purchase, including installation:                   " & strPrice

This part is your issue:

Range("A47").Font.Bold = True

Change this to:

Iif(Range("A47").Font.Bold = True,True,False)

Similarly for the other strings.

The final code

Based on feedback from your comments your code should look like this:

strProducts = strProducts & Chr(11) & Range("A47").Value & Chr(11) & "Purchase, including installation:                   " & strPrice
Range("A47").Font.Bold = True