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
The solution
Taking the example of the first string:
This part is your issue:
Change this to:
Similarly for the other strings.
The final code
Based on feedback from your comments your code should look like this: