VBA - Replace blank cells

Question

I have to replace each blank cell in the column BM with the data in this same row in column DN. I have written that:

Columns("BM:BM").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=DN2"

But the "=DN2" ends like "=@'DN2'" in every cell. Which makes an error. Does someone know how could I get the cell in DN from the same row please?

Thank you very much in advance!

Answer 1

If you are going to R1C1 then make the Formula R1C1

If Application.CountA(ActiveSheet.Range("BM:BM"))<> ActiveSheet.Rows.Count Then
    ActiveSheet.Range("BM:BM").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC118"
End If

Answer 2

Another way to do this

Dim rng As Range

On Error Resume Next
Set rng = Columns("BM:BM").SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rng Is Nothing Then
    rng.Formula = "=DN" & rng.Row
End If

BTW if you are using SpecialCells, use error handling else your code will crash when it doesn't find those cells