VBA Check a column for blanks, copy 1 column previous if a blank is found.

solved

I've had a few working implementations, but occasionally getting #NA values in the column after executing the VBA macro. All I want is when Column I has a blank, add the date from column H. Any ideas?

Column 1 is dates (Short), Column 2 is dates (Short). Code Attempts below

Sub CSD()
Dim r As Range
lrow = Range("A" & Rows.Count).End(xlUp).Row
'Set r = Range("I2:I" & lrow)
For Each r In Range("I2:I" & lrow)
    If r = "" Then r = Val(r.Offset(, -1))
Next
End Sub

And this is my other attempt.

Sub CSD_2()
lrow = Range("A" & Rows.Count).End(xlUp).Row
Dim rng As Range
Set rng = Range("I2:I" & lrow)

With rng.SpecialCells(xlCellTypeBlanks)
    .FormulaR1C1 = "=RC[-1]"
    .Value = .Value
End With
End Sub

Posted Jul 31, 2017, 3:20 PM UTC · edited

77
Loading comments…