Macro to insert/type nonformatted text, even if after sub/superscript

[agrarian]

I'm working on a macro to copy the contents of selected cells and paste them at the end of a document. I do this in a loop of For Each wCell In selRange.Cells. I've worked it out so that I am just copying the contents of the cells and not the table cells themselves. So the cell contents don't just run together, I want to insert a space in-between the cells contents.

My problem occurs when the last character before the insert is a sub or superscript. In that case, the space inserted is very tiny (sub/superscript sized) and not a normal size. I have tried different things, but so far have not been able to figure out the secret. I am not very experienced with VBA.

This is the code I've tried:

Dim selRange As Word.Range
Dim wCell As Word.Cell
Dim rowCount As Integer
Dim lastRow As Integer
Dim currentRow As Integer
Dim currentColumn As Integer
Dim RowsSelected() As Integer
Dim newline As String
Dim space As String
Dim isFirstCellOfSelection As Boolean

lastRow = 0
rowCount = 0
isFirstCellOfSelection = True


'Save the selected range so we can iterate through it as well as create new selections
Set selRange = Selection.Range



For Each wCell In Selection.Cells
If wCell.RowIndex <> lastRow Then
ReDim Preserve RowsSelected(rowCount)
RowsSelected(rowCount) = wCell.RowIndex
rowCount = rowCount + 1
End If
lastRow = wCell.RowIndex
Next


lastRow = 0
For Each wCell In selRange.Cells
' Determine if we need space or newline separators
If wCell.RowIndex <> lastRow Then
' Looking at new row
space = ""
newline = vbCrLf
isFirstCellOfSelection = False
Else
If isFirstCellOfSelection Then
space = ""
newline = ""
isFirstCellOfSelection = False
Else
space = " "
newline = ""
End If
End If
wCell.Select
' Just want the contents, not the whole cell itself
Selection.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
currentRow = wCell.RowIndex
currentColumn = wCell.ColumnIndex
Selection.Cut

Selection.Find.ClearFormatting
With Selection.Find
.Text = "}" ' This indicates what I'm calling the end of the file
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute
End With


If Selection.Find.Found Then
Selection.Collapse wdCollapseStart
' Different things I've tried here:
' Selection.ClearCharacterAllFormatting (got error)
' Selection.ClearDirectFormatting
Selection.ClearFormatting
Selection.TypeText Text:=space
Selection.ClearFormatting
Selection.TypeText Text:=newline
Selection.ClearFormatting
Selection.PasteAndFormat Type:=wdFormatOriginalFormatting

Else
If MsgBox("There was NO closing brace found, Would you like to insert the text at the end of the file?", vbQuestion Or vbYesNo Or vbSystemModal) = vbYes Then
selRange.Cut
Selection.EndKey wdStory
Selection.InsertAfter vbCrLf & sFootNoteText
Selection.Collapse wdCollapseEnd
End If
End If
lastRow = wCell.RowIndex
Next

[agrarian]

Is there a way to determine if the selection point is inside of a sub/superscript? If so, perhaps I could just toggle it off? Looking for ideas.

[Chas Kenyon]

Is there a way to determine if the selection point is inside of a sub/superscript? If so, perhaps I could just toggle it off? Looking for ideas.

Consider changing the selection to the Normal style or other base style before you add your text.

[agrarian]

I found a solution! What I needed to do was select the space after I had inserted it and then call selection.ClearFormatting. The red code above has been modified as shown below:


Selection.Collapse wdCollapseStart
' if inserting space, be sure it is unformatted (not affected by text immediately before it)
If space <> "" Then
Selection.InsertAfter (space)
Selection.ClearFormatting
End If
Selection.Collapse wdCollapseEnd
Selection.InsertAfter (newline)
Selection.Collapse wdCollapseEnd
Selection.PasteAndFormat Type:=wdFormatOriginalFormatting

[agrarian]

Thanks for your reply! By the time I received it, I had come up with the answer below.