Copy Formula to empty Cell in Range and then duplicate to the end of the range

[jjtechtech]

I have an instance where I would like a formula restored to a range of cells in the event that it is removed by a user. I would like the formula restored beginning with the first empty cell in the range.

Sample

Sample Formula to restore = I5
Sample Range = C2:C16
In this instance the first empty Cell in the Range would be C5
I would like the formula copied to C5:C16

 

[Fluff]

Hi & welcome to MrExcel.
Do you just want to put the formula in the blank cells within C2:C16?

 

[jjtechtech]

Yes. Sorry been searching for ways to get a sample picture added to better describe what I am trying to do.

 

[Fluff]

How about

Sub jjtech()
    On Error Resume Next
    Range("C2:C16").SpecialCells(xlBlanks).FormulaR1C1 = "=rc9"
    On Error GoTo 0
End Sub

 

[jjtechtech]

Wow! That was quick. Where would I tell it the location of the formula to copy? Let's say the formula to copy is in cell I5?

 

[jjtechtech]

I tried running the vba code and it basically copied the text that was in cell I5 but not the formula. C5's formula was then =$I5...

 

[jjtechtech]

Thanks for all your help. Was finally able to get the following code to work for me.
Sub FormulaRestore()
On Error Resume Next
Range("I5").Copy
Range("C2:C16").SpecialCells(xlBlanks).Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
On Error GoTo 0
End Sub

 

[Fluff]

Glad you sorted it & thanks for the feedback