cell.formula fails

Hi All:

I'm transforming some Excel data into Word tables. I'm quite familiar
with Word VBA but having some difficulty with Excel. (This inexperience
may explain why I feel Excel's VB is flakier than Word's.)

One problem I've just encountered is this test:

for each acell in myrange.cells
if acell.formula="" then acell.value = " "
next acell

That is, empty cells have a blank inserted in them. I do this because
if you copy a range from Excel to Word, empty cells are omitted from
the Word table, leading to a real mess.

The comparison fails with a 1004 error after some time. Examination of
the cell shows it contains about 20 lines of text, otherwise
unremarkable.

Any suggestions?
Garry

Dim rng as Range
On Error Resume Next
set rng = myrange.SpecialCells(xlBlanks")
On Error goto 0
if not rng is nothing then
rng.Value = " "
End if

--
Regards,
Tom Ogilvy


"Garry" <[email protected]> wrote in message
news:[email protected]...
> Hi All:
>
> I'm transforming some Excel data into Word tables. I'm quite familiar
> with Word VBA but having some difficulty with Excel. (This inexperience
> may explain why I feel Excel's VB is flakier than Word's.)
>
> One problem I've just encountered is this test:
>
> for each acell in myrange.cells
> if acell.formula="" then acell.value = " "
> next acell
>
> That is, empty cells have a blank inserted in them. I do this because
> if you copy a range from Excel to Word, empty cells are omitted from
> the Word table, leading to a real mess.
>
> The comparison fails with a 1004 error after some time. Examination of
> the cell shows it contains about 20 lines of text, otherwise
> unremarkable.
>
> Any suggestions?
> Garry
>