Fill blank cells

[agibsonsw]

Hello again. Excel 2003 VBA.

I'm using SpecialCells to identify blank cells and setting:

Code: Select all

rngBlanks.FormulaR1C1 = "R[-1]C" '(to copy from the row above), and then
rngBlanks = rngBlanks 'to convert these to values.

But it misbehaves - I obtain #N/A for cells further down in the area.

Any idea why? Thanks, Andy.

[HansV]

In the first place, the formula should be "=R[-1]C" (formulas must start with =).

In the second place, you should use the complete range (not just rngBlanks) to convert the formulas to values. For example, if you had

Set rngBlanks = Selection.SpecialCells(xlCellTypeBlanks)

the last line should be

Selection.Value = Selection.Value 'to convert these to values.

[agibsonsw]

Thank you. Andy

(I did have the = sign in my original, but mistyped it in the post.)

I was hopeful of only setting rngBlanks to values, as there may be other formulas in the column that
I need to leave alone.

Is there a way to do this? Thanks, Andy.

[agibsonsw]

Actually, I've managed to achieve this using:

Code: Select all

rngBlanks.FormulaR1C1 = "=R[-1]C"
                For Each rng2 In rngBlanks
                    rng2 = rng2.Value
                Next rng2

I'm having to loop through each blank cell, but at least it will keep any existing formulas in the worksheet.

Ta, Andy.

[HansV]

Good for you!

BTW you added the question about preserving formulas after I had already read your original reply, so I never saw the addition. It's better to post additional questions in a new reply.

[agibsonsw]

That's sensible advice. I should use the Edit option only to make minor corrections and should do so soon after
the original submission.
Andy.