Auto fill formula into blank cells

[Jbigler]

What I want: I am looking for a quick way to have multiple blank cells in a ROW take on the FORMULA of the cell above.


I do not want: To highlight all the blank cells and then type in the formula "=cell above" and press Ctrl Enter. That would give me the VALUE of the cell above not the formula.


Basically I have a row that follows the pattern: 2 static values, 2 blank cells, 2 static values etc. I am manually selecting the range above the blank cells to auto fill the formula down. I am not sure why highlighting blanks via the special command does not allow for CTRL +D to work?


Any Ideas?

 

[jason]

not sure if im following exactly... but perhaps try this.


since it sounds like you'll have a set patter (2 with the fomula & 2 blank) put the formula in the first 2 cells and then leave the next 2 blank. then select all 4 of these cells and use the Fill Handle (the black box at the bottom-right corner of your selected cells) and drag it to the right. it should keep the pattern that you selected and fill the formula and blank cells down accordingly.


again, not sure if this end result is what your looking for...?


if i'm way off, could you please explain again and maybe with an example of how the data is laid out and also showing what you're wanting

 

[Luke M]

There might be a built in way to do this, but here's how I'd do it since it's a simple enough script. Here's the macro to copy formula from above:

[pre]

Sub CopyFromAbove()
Dim c As Range
For Each c In Selection
c.Formula = c.Offset(-1, 0).Formula
Next
End Sub
Since you'll be doing this a lot, I'd assign the macro to a shortcut key (perhaps Ctrl+Shift+D)


The regular Ctrl+d isn't working because it can't handle multiple selections. =/


For reference, this is the setup I think you have

row 1	static	static	formula	formula	static	static	formula	formula	static	static
row 2	static	static	<need>	<need>	static	static	<need>	<need>	static	static

[/pre]
Desire is to fill all the need cells with formula from row above.

 

[Jbigler]

LUKE M. That is exactly the setup I have and exactly what I was looking for. Thank you for your help!!