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

Question

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

hansvogelaar · Answer

KevinAP&nbsp;
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(""*""&amp;RC2,'Fixed Assets sorted'!R3C1:R500C29,2,FALSE))"
    End If
End Sub

kevinap · Answer

HansVogelaar&nbsp;Thanks,Still having trouble. Does this information go in the sheet code or the module code?

hansvogelaar · Answer

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

Forum Discussion

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

3 Replies

Resources