Paste a Formula into Non-Empty cells in Excell

November 10, 2010 2:16 AM

Excel VBA Macro Help: I am trying to come up with a very basic script to paste a formula into any Non-Empty cells in the currently selected Range. but its not working. argh!

I dont' really get Exce3l VBA syntax and just hack things together but basically I want some Code that will do the following:

For each cell in the currently selected range:

If the cell is empty / blank then do nothing.

Else
Paste the current Copy Buffer (into which I would have already 'copied' the relevant formula).
End If
Next cell

posted by mary8nne to Computers & Internet (8 answers total)

Selection.SpecialCells(xlCellTypeBlanks).Select
ActiveSheet.Paste

Should do the trick?
posted by Simon_ at 2:36 AM on November 10, 2010

Response by poster: Um, No.
That does the opposite to what I want ; That pastes into the Empty Cells. Whereas I want to paste into the NON-EMPTY cells. (ie over-write what is there).
posted by mary8nne at 2:40 AM on November 10, 2010

Response by poster: Oh if i use this instead:
xlCellTypeFormulas

I think it might work
posted by mary8nne at 2:41 AM on November 10, 2010

Serves me right for not reading the spec properly.

Try:

Selection.SpecialCells(xlCellTypeConstants, 23).Select
ActiveSheet.Paste
posted by Simon_ at 2:46 AM on November 10, 2010

Response by poster: Oh one of the problems is that some of the cells I want to over-write contain formulae and some contain constants.
posted by mary8nne at 2:54 AM on November 10, 2010

http://msdn.microsoft.com/en-us/library/Aa192210 might help.
posted by Simon_ at 3:51 AM on November 10, 2010

If you're still stuck with this, feel free to memail me and I'll look over your document.
posted by Simon_ at 8:47 AM on November 11, 2010

Dim rng, oCell As Range

Set rng = Range("A1:E1")

For Each oCell In rng
If Not IsEmpty(oCell) Then
oCell.Select
ActiveSheet.Paste
End If
Next
posted by marco_nj at 9:11 PM on November 21, 2010