Insert Headers into Entries in Table

[Rishek]

I need to reformat a table slightly to use for extracting values as described in this post. (http://www.vbaexpress.com/forum/showthread.php?59600-Extract-most-recent-quot-Header-quot-into-table)

Here's a sample with before and after values: 19614

Here's what I'd like to do:

1. Insert the "Header" values (e.g. "Coach1", "Coach2" etc.) into their respective boxes as shown. So that this:



Coach1
Pretend this row is merged
Pretend this row is merged


1:00 - 2:00pm
Bill
PLACE



becomes this



Coach1
Pretend this row is merged
Pretend this row is merged


1:00 - 2:00pm
Bill, Coach 1
PLACE



I also have a lot of rows in these boxes that direct the reader to "See SECTION". I would like to clear the contents of these cells as well as the cells to the immediate right and left so that this:



3:00 - 4:00pm
See ABOVE
PLACE



becomes three empty cells. I think that this is a fairly basic find and replace job, since any time the combination "See X" appears, it's a row that should be blank, but I'm not quite sure how to affect the cells on either side.

Help as always, appreciated.

Edit: Two additional pieces of information:
Coach11 has a blank space. Normally the words "Office Hours" appear here. I'm planning to eliminate this at an earlier formatting stage using a simple find/replace macro.
This table is typically the 4th table in a larger document, if that makes any difference to how one would code it.

[Rishek]

Have a cludgy work around for deleting the "See SECTION" stuff since I've discovered that the next macro I'm running won't extract an if the middle cell with the names is empty. I can just macro a find and replace for each different "See SECTION" entry to replace it "" (i.e. delete it) and I'm in business. Does not seem elegant, but it'll do. I'm going to call this progress.

[Rishek]

So I learned a ton trying to figure this out, but I'm not quite there yet. Here's what I have:



Sub CoachingAdd2()
Dim oTable As Table
Dim oRow As Row
Dim oRng As Range
Dim oCell As Range, nCell As Range
On Error Resume Next
Set oTable = ActiveDocument.Tables(1)
For Each oRow In oTable.Rows 'getting the name to insert
If oRow.Cells.Count = 2 Then
Set oCell = oRow.Cells(1).Range
oCell.End = oCell.End - 1
Set oRng = oRow.Cells(1).Range
oRng.End = oRng - 1
ElseIf oRow.Cells.Count = 6 Then
Set nCell = oRow.Cells(2).Range
nCell.Collapse (wdCollapseStart) 'not having this makes the macro replace the cell's text. Still don't quite get that
With nCell
.Text = oRng & ", " 'inserting the gathered name with a comma and space afterwards
End With
End If

Next oRow


This works very well (major thanks to those who gave me the basis code here (http://www.vbaexpress.com/forum/showthread.php?59867-Autofill-in-Table)) at getting the correct coach name into the correct boxes, EXCEPT:

a) it inserts the name with a line break directly afterwards and then the comma I have put in above. I want to eliminate this line break.

b) it also does this in empty cells, which I would prefer it not do. I know it's some command like "If Not Len(SOMETHING) > 0 Then" or some such, but I can't figure out what or where.

To date this is the most complicated bit of vba tinkering I have accomplished on my own. I'm quite stoked about that.

[Rishek]

Cracked it.



Sub CoachingAdd()
Dim oTable As Table
Dim oRow As Row
Dim oCell As Range, nCell As Range
Dim oStr As String
On Error Resume Next
Set oTable = ActiveDocument.Tables(1)
For Each oRow In oTable.Rows
If oRow.Cells.Count = 2 Then
Set oCell = oRow.Cells(1).Range
oCell.End = oCell.End - 1
ElseIf oRow.Cells.Count = 6 Then
oStr = oRow.Cells(2).Range
If Len(oStr) > 2 Then
Set nCell = oRow.Cells(2).Range
nCell.Collapse (wdCollapseStart)
With nCell
.Text = oCell & ", "
End With
Else
End If
End If
Next oRow
End Sub


Don't quite understand why using "oRng" above gave me the line break, but it did. Turns out I needed a String to check cell length. There. My first piece of derivative code.