Macro to search Table in MS word for blank cells

[hugh999]

Hi

I have table in a word file that contains 2 columns. What i am trying to do is search through the second column for blank cells and if found copy the text from the adjoining cell that is in the first column to the blank cell.

I found some code for searching blank cells but i need help on copy the text from the adjoining cell to the blank cell.

Dim oCell   As Word.Cell
     
    With Selection
   
        If .Information(wdWithInTable) Then
    
            For Each oCell In .Tables(1).Range.Cells
    
                    ' copy the text from the adjoining cell to the blank cell

                End If
            Next
        End If
    End With

I appreciate any help

Thanks

 

[Bong]

Use the cell.rowindex and cell.columnindex properties.

so, oCell.columnindex - 1 will give the cell to the left's column. Then you can use an alternative syntax, thisdocument.Tables(1).Cell(r,c) to specify row and column.

_________________
Bob Rashkin

 

[fumei]

Option Explicit
Function CellText2(strIn As String)
    CellText2 = Left(strIn, Len(strIn) - 2)
End Function

Sub TableCellBesideMe()
Dim oTable As Table
Dim oCell As Cell
Dim var

If Selection.Information(wdWithInTable) Then
   Set oTable = Selection.Tables(1)
   For var = 1 To oTable.Rows.Count
      If CellText2(oTable.Cell(var, 2).Range.Text) = "" Then
         oTable.Cell(var, 2).Range.Text = _
            CellText2(oTable.Cell(var, 1).Range.Text)
      End If
   Next
End If
End Sub

You need the text function because extracting text out using .Range.Text - even to test if it is "" - also extractes the end-of-cell marker.

In other words, without stripping off the end-of-cell marker, Table.Cell(x,y).Range.Text is NEVER "".

So the code makes a table object of the table the selection is in (assuming it is in a table), loops through each row, checking if Column 2 is "blank", and if it is, makes its text = the text of the Column 1 of the same row.

Gerry

 

[fumei]

Or...using columnindex, an alternative. Note that the function used to get the text (stripped of the end-of-cell marker) uses a cell object this time, rather than the string of the Range.

Function CellText(oCell As Cell)
   CellText = Left(oCell.Range.Text, _
      Len(oCell.Range.Text) - 2)
End Function

Sub TableCellBesideMe_Ver2()
Dim oTable As Table
Dim oCell As Cell
Dim var

If Selection.Information(wdWithInTable) Then
   Set oTable = Selection.Tables(1)
   For Each oCell In oTable.Range.Cells
      If oCell.ColumnIndex = 2 Then
         If CellText(oCell) = "" Then
            oCell.Range.Text = CellText(oCell.Previous)
         End If
      End If
   Next
End If
End Sub

hat way, you can pass oCell.Previous - the previous cell, as an object - to the function. Note also that uses ColumnIndex has (commonly) a tie-in to using Range.Cells - rather than Table.Cells(x,y).

Table.Range.Cells iterate through the cells in order, regardless of row or column.

Gerry

 

[hugh999]

Thank you for the help, the code works great.

Thanks

 

[macropod]

Hi hugh999,

Please see:

http://www.excelguru.ca/node/7

It seems you've posted the same question as 'newtomacros' at:

http://www.techsupportforum.com/mic...fice-support/483896-macro-table-ms-word.html,

where I've just finished providing essentially the same solution Gerry's already provided. I am not amused to find I've been wasting my time because you didn't have the courtesy to tell anyone you'd cross-posted in other forums.


Cheers
[MS MVP - Word]

 

[fumei]

Yes, hugh999, please read the link macropod posted. It is not that we say you should never post in multiple places, but if you DO...it is polite to say so (and where).

I too have had the experience of posting a full response to someone - taking time and effort to figure out whatever the solution was - and then finding out someone else (HEY! I think it was macropod....just kidding) had already posted essentially the same response somewhere else.

In other words, my effort was extraneous and basically a waste of my time.

We are not trying to be nasty, we are requesting courtesy - and telling you why.

Gerry

 

[hugh999]

Hi

I posted to this forum as i did not receive any reply from the other forum in 24 hours, so assumed it would not get answered.

I apologize if you feel i wasted your time.

Thanks

 

[fumei]

Receive a reply? No one receives a reply. A reply is posted. You have to go and look...and there is no time frame.

Gerry

 

[hugh999]

I did not wait for a reply, i checked the post in regular intervals.

How long should a person wait: 24 hours, 48, hours, 96, hours or until they are so old they have forgotten that they actually submitted a post.

From my experience with forums if there has not been any updates to a post in 24 hours then i can take it that there will not be any update/reply to the post.

 

[fumei]

From my experience with forums if there has not been any updates to a post in 24 hours then i can take it that there will not be any update/reply to the post."

Generally speaking...yes I suppose that is a fair assessment. Although, often people will post a "Bump" post with a question mark to re-ask.

But, again, it is NOT the posting elsewhere that is the issue. Go ahead if you feel you have not received a response. But tell us. Something as simple as:

"Also posted to other forum site."

Gerry

 

[SkipVought]

Again, the issue is not that you posted in multiple forums.

This issue is that having done so, you failed to post a notification in EACH forum/thread that you had multi-posted, for the reasons stated above.

Can you understand that?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[hugh999]

okely dokely neighbor

 

[fumei]

Whew, this seems way heavier than it needed to be.

Gerry