How to insert paragraphs between text and then export to word

attikuz

New Member
Joined
Jul 23, 2013
Messages
26
Office Version
  1. 365
Hello!

I have some code that combines text together. Because of the number of characters I have to export the text to word. I have two issues:

1) The formatting between the text is not correct. I want to apply spacing between my variables and then a page break between each new row.

2) I am really struggling to export to word and autofit the contents.

Here is my code:

VBA Code:
Sub inbrief()

Dim Cash As Range
Dim Title, activity, Description As String
Dim cell As Range
Dim CurrentRow, LastRow As Integer

Application.ScreenUpdating = False

CurrentRow = 3
 
'Clear sheet and filters
Sheets("In Briefs").Activate
Sheets("In Briefs").Range("$A$2:$H$1000").AutoFilter Field:=3
Sheets("In Briefs").Range("A3:H1000").ClearContents

'Copy SEDOLs from portfolio tab and bring in in briefs
Sheets("Portfolio").Range("A8:A100").Copy
Sheets("In Briefs").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

'Remove all the blank rows
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A3:A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy the vlookup formulas to import the paras
Range("B1:D1").Copy
Range("B3:D" & LastRow).PasteSpecial xlPasteFormulas

'Join the header, activity and inbrief together and apply formatting
For Each cell In Range("F3:F" & LastRow)

If Range("C" & CurrentRow) <> "" Then
    With Application
        Title = Range("B" & CurrentRow).value
        Description = Range("C" & CurrentRow).value & Chr(13)
        activity = Range("D" & CurrentRow).value
    End With
        
        cell.value = Title & activity & Description
        
    With cell
        .Characters(1, Len(Title)).Font.Color = RGB(0, 89, 85)
        .Characters(1, Len(Title)).Font.Size = 18
        .Characters(Len(Title) + 1, Len(activity)).Font.Size = 11
        .Characters(Len(Title) + 1, Len(activity)).Font.Color = vbBlack
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Size = 11
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Color = vbBlack
     End With
End If

CurrentRow = CurrentRow + 1
Next

'Filter out any securities that have no data
With Range("$A$3:$H$" & LastRow)
        .AutoFilter Field:=3, Criteria1:="="
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With

Application.ScreenUpdating = True

End Sub

I've attached a screenshot of what it looks like.

Please can someone help me with the formatting and with exporting to word?

Thanks!
 

Attachments

  • Capture.PNG
    Capture.PNG
    64.4 KB · Views: 38

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Not sure I can help but maybe this thread will provide some direction.
To add a new paragraph, use vbCrLf or Chr(13) & Chr(10). I could only see that you added Chr(13), which is not correct.
To add space between variables try concatenation: variableName1 & " " & variableName2
In a multi line declaration, you must explicitly type each variable as in
Dim Title As String, activity As String, Description As String

In your line, only Description is a string, the rest are variants because you didn't specify the data type. As long as the code can correctly interpret what you pass to the variable it will probably work. However, coding as if you expect a variable to hold a string when it can hold any data type is not a good idea.

Description might be a vba reserved word - it's a reserved word in Access but I doubt it will cause any issues for you. After this, I'm going to look for a list of reserved words for Excel.

As for your issue #2, I don't know enough about it to be of any help.

EDIT- apparently it is reserved. About half way down the page here is an Excel list
 
Last edited:
Upvote 0

Forum statistics

Threads
1,222,095
Messages
6,163,899
Members
451,865
Latest member
dunworthc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top