VBA Check a column for blanks, copy 1 column previous if a blank is found.
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