Re-enter a formula after deleting cell data in a column of cells

New Contributor

How do I write the macro to re-insert a formula after cells in the range have been overwritten and then cleared?

What I have so far is:

 

Sub Macro1()
'
' Macro1 Macro
'
If Range("C15").Value = "" Then
Range("C15").Formula = "=IF(RC2=0,,VLOOKUP(""*""&RC2,'Fixed Assets sorted'!R3C1:R500C29,2,FALSE))"
End If
End Sub

This works but only on the specific cell noted.

I've tried to extend the range to something like: range("C15:C900") but it only works if all those cells are blank because it is looking at all of them to have data or be blank.

I would like it to do this process for each cell in my row range individually.

 

Thanks

 

 

3 Replies

@KevinAP 

Like this:

Sub Macro1()
    Dim rng As Range
    On Error Resume Next
    Set rng = Range("C15:C16").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not rng Is Nothing Then
        rng.FormulaR1C1 = "=IF(RC2=0,,VLOOKUP(""*""&RC2,'Fixed Assets sorted'!R3C1:R500C29,2,FALSE))"
    End If
End Sub

@Hans Vogelaar 

Thanks,

Still having trouble. Does this information go in the sheet code or the module code?

@KevinAP 

It is an ordinary macro, so it should be copied into a standard module, not into a worksheet module.