Forum Discussion

Copper Contributor

Dec 20, 2021

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

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 ...

excel

Macros and VBA

HansVogelaar

MVP

Dec 20, 2021

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

KevinAP

Copper Contributor

Dec 20, 2021

HansVogelaar

Thanks,

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

HansVogelaar
MVP
Dec 20, 2021
KevinAP

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