Dec 20 2021 07:33 AM - edited Dec 20 2021 08:08 AM
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
Dec 20 2021 11:40 AM
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
Dec 20 2021 01:27 PM
Thanks,
Still having trouble. Does this information go in the sheet code or the module code?
Dec 20 2021 01:29 PM
It is an ordinary macro, so it should be copied into a standard module, not into a worksheet module.