Copy from one cell and paste into next if empty

[sunjan]

Hi all,

I'm a newbie, so apologies in advance.

I have a Word document that consists of several tables. The tables are either one or two cells wide. The left cell always contains text, but the right cell either contains text or is empty. I intend to prepare the document for a translation tool called Trados.

I'm trying to write a script to does the following (two operations):

1.
Select the entire document
Apply the hidden text attribute to the selection
Apply no spellcheck to the selection

2.
Select the content of the first cell in the first table
If there is an empty cell to the right, paste the selection into the right cell.
Apply unhidden text attribute to the right cell
Apply spellcheck to the right cell
If there is no empty cell to the right, just go to next cell
Repeat until the last cell

I hope the description makes sense?!
AFAIK, I should use these lines, but I need help to get everything together:


Selection.Font.Hidden = True
Selection.LanguageID = wdNoProofing
Selection.MoveRight Unit:=wdCell
If Selection.Next(wdCharacter, -1) = Chr(13) & Chr(7) Then Selection.Paste

Selection.Font.Hidden = False
Selection.LanguageID = wdProofing

Thanks a lot for any help!

/Jan

[fumei]

Let me see if I have this correctly.

1. you want to hide EVERYTHING, the entire document

2. then test to see if the EACH cell in Column 1 of EACH table has text in SameRow/Column 2

3. if that cell in Column 2 is empty, then copy over the Hidden text of the cell in Column 1, into Column 2

4. UnHide that text (in cell column 2), and spell check it.

Correct?

[sunjan]

Let me see if I have this correctly.

1. you want to hide EVERYTHING, the entire document

2. then test to see if the EACH cell in Column 1 of EACH table has text in SameRow/Column 2

3. if that cell in Column 2 is empty, then copy over the Hidden text of the cell in Column 1, into Column 2

4. UnHide that text (in cell column 2), and spell check it.

Correct?

Thanks so much for your input Fumei. I've read many of your posts here and learnt a lot!

1. Correct. (The reason for applying hidden is that all elements will be skipped by my translation software. This is intentional.)
2. Correct.
3. Correct.
4. Correct. The unhidden text will be available for my translation software. (So I won't actually spellcheck cell 2 at this stage. I merely want to allow spellchecking, so that I can proofread it after translation.)

Fumei, I understand that you're also very good with logical thinking - to look at a problem and come up with a solution that requires the least number of loops and iterations. I'm so glad you're willing to look into this!:bow:

[Jay Freedman]

Since fumei has done the hard work of stating concisely the steps of the procedure, I'll supply the relatively simple implementation step:

Sub demo()
Dim oTbl As Table
Dim nRow As Long
Dim rgCheck As Range, rgSrc As Range

' Hide everything
ActiveDocument.Range.Font.Hidden = True
ActiveWindow.View.ShowHiddenText = True

' Process each table
For Each oTbl In ActiveDocument.Tables
With oTbl
' Process each cell in column 1
For nRow = 1 To oTbl.Rows.Count
Set rgCheck = .Cell(nRow, 2).Range
' Exclude the cell marker
rgCheck.MoveEnd wdCharacter, -1
' Ensure the range's Text includes the hidden text
rgCheck.TextRetrievalMode.IncludeHiddenText = True

' Process row only if col 2 is empty
If Len(rgCheck.Text) = 0 Then
Set rgSrc = .Cell(nRow, 1).Range
rgSrc.MoveEnd wdCharacter, -1
rgSrc.TextRetrievalMode.IncludeHiddenText = True

' Copy col 1 to col 2
rgCheck.FormattedText = rgSrc.FormattedText

' Unhide col 2
rgCheck.Font.Hidden = False

' Spell check
rgCheck.CheckSpelling
End If
Next nRow
End With
Next oTbl

ActiveWindow.View.ShowHiddenText = False
End Sub


You should add error-checking to this, for instance to catch the case of a table that contains merged or split cells (which would otherwise make the macro stop) or a table that doesn't have a second column.

--
Jay

[sunjan]

You should add error-checking to this, for instance to catch the case of a table that contains merged or split cells (which would otherwise make the macro stop) or a table that doesn't have a second column.

--
Jay

Hi Jay,

Thanks a million for your expertise.
You're right about the error checking, there are in fact some tables with one column only, which should be skipped.
It stalls on this line:

Set rgCheck = .Cell(nRow, 2).Range

Is there any easy way to make it ignore tables with one column?

Thanks again,

/Jan

[Jay Freedman]

There are a couple of ways to handle it, depending on how certain you are about the document's contents. If you know that there are no split or merged cells, but that some tables were built with only one column, you could insert this line right after the "With oTbl" line:

If .Columns.Count > 1 Then

and put the line

End If

before the "End With" line.

If there could be split or merged cells, then the ".Columns.Count" expression would cause an error -- VBA really, really doesn't like split and merged tables! The solution then involves inserting the line

On Error GoTo ErrHdl

near the top of the macro, and putting the label

ErrHdl:

before the "Next oTbl" line. This can get more complex if you want to check the error code and maybe display a message, but that would be the minimum.

--
Jay

[fumei]

Jay: "VBA really, really doesn't like split and merged tables! "

True.

However, this is only fully true is you are using cells as child objects of the table; i.e. the syntax is:

TableObject.Cell(rowndex, colIndex)

This syntax requires row and column values.

If you use the Cells collection of the table Range, then the identifying parameter is a single value, the index number of the collection.
[vba]
TableObject.Range.Cells(x)
will process through split/merged cells as they are considered just cells within that collection.

Thus, using Range.Cells avoids the error that the OP has with:

Set rgCheck = .Cell(nRow, 2).Range


sunjan, regarding: "Is there any easy way to make it ignore tables with one column?", as Jay mentions this is fairly easy, simply test the column count.

Please, I am NOT trying to be critical, but to further my rant about logic. I posted:

2. then test to see if the EACH cell in Column 1 of EACH table has text in SameRow/Column 2

To which you replied:

2. Correct.

Except...it is NOT quite correct.

Notice that I had uppercased EACH for each table. The correct logic is NOT for each table. The correct logic is to process tables that have two columns. If the table has one column...do not process. Not EACH. By putting that rather critical piece of logic at the start, you can avoid VBA both erroring out (as has happened for you), but it is more efficient, as VBA first tests the column count...if it is 1, then fugeddaboudit, and look at the next one.

Here is some alternative coding.
Option Explicit

Function CellText(strIn As String) As String
CellText = Left(strIn, Len(strIn) - 2)
End Function

Sub demo()
Dim oTbl As Table
Dim oCell As Cell

' Hide everything
ActiveDocument.Range.Font.Hidden = True
ActiveWindow.View.ShowHiddenText = True

' Process each table
For Each oTbl In ActiveDocument.Tables
' but ONLY if column count > 1
If oTbl.Columns.Count > 1 Then
For Each oCell In oTbl.Range.Cells
' ignore cells with columnIndex = 1
If oCell.ColumnIndex = 2 Then
' if cell text is ""
If CellText(oCell.Range.Text) = "" Then
' make its text the previous cell text
oCell.Range.Text = _
CellText(oCell.Previous.Range.Text)
End If
' unhide the text in column 2
oCell.Range.Font.Hidden = False
End If
Next
End If
Next

ActiveWindow.View.ShowHiddenText = False
End Sub



Jay: "Since fumei has done the hard work of stating concisely the steps of the procedure, I'll supply the relatively simple implementation step:"

Coming from you, that is complimentary indeed. It emphasizes my point that the hard part of a lot of things is the concise determination of the steps. The coding is (relatively) simple once one does the determination of WHAT (really) needs to be done.

BTW: if you are commonly getting/using text from table cells, it is a good and convenient thing to use a Function to get that text. Yes, you can use Len on the .Range.text to fuss with the end-of-cell marker, but again, if you are going to be doing this often, have a dedicated Function.

That is what the function CellText does. It uses the string from a cell range (which includeds the end-of-cell marker). The string is passed to the function. The function returns a string without the end-of-cell marker.

Depending on your requirements, it may be more useful to pass the cell itself as an object to a function. Here is the alternative function - that still returns the text - but uses a cell object instead.

Function CellText2(aCell As Cell) As String
Dim sText As String
sText = aCell.Range.Text
CellText2 = Left(sText, Len(sText) - 2)
End Function

In the above instead of the cell range.text being passed into the function as a string, the cell object itself is passed into the function.

Why would you use the cell object, rather than a string? Well, if you want to added other processing. Say you wanted to get the text of one cell, copy it over to another cell - like what you are doing - AND also do something to the original cell text (say make it bold).

Function CellText2(aCell As Cell) As String
Dim sText As String
sText = aCell.Range.Text
acell.Range.Font.Bold = True
CellText2 = Left(sText, Len(sText) - 2)
End Function


Now, if the original code, the instruction:


' make its text = the previous cell text
oCell.Range.Text = _
CellText(oCell.Previous.Range.Text)

would copy over the text into Column 2, AND make the original cell text bold (as it is an instruction within the Function).