Problem with VBA excel to fill empty cell in a column only if a specific condition is met in another-CodePudding

I am new to VBA and I've been trying to figure this problem out for a while. In column "D" I have a few empty cells that needs to be filled with the above value in the same column only if a condition in another column is met. For each empty cell in column D, I am trying to have VBA copy the value of the cell -1 (the cell above) only if : The word in the cell of the same row in column E is equal (=) the word in the cell above in column E.

If this condition is met then VBA should copy the value of the cell -1 in column D in the empty cell.

And if the condition is not met then put N/A in the empty cell of column D.

I wrote this code so far and it seems to work but I wonder if there is a better way to achieve the same result. Also, I don't know how to tell the code to write an error message or a "N/A" in the cell where the condition is not met.

Sub ...()

Dim columnValues As Range
    Dim i As Long
Dim columnref As Range

Set columnValues = Range("D2:D2000")
Set columnref = Range("E2:E2000")

For i = 1 To columnValues.Rows.Count

If columnValues.Cells(i, 1).Value = "" And columnref.Cells(i, 1).Value = columnref.Cells(i - 1, 1).Value Then
    columnValues.Cells(i, 1).Value = columnValues.Cells(i - 1, 1).Value

    End If
Next

End Sub

Thank you :)

CodePudding user response：

I don't understand why you need VBA for this:

I just selected all cells in the column with the letters "aaa", "bbb", ..., I pressed Ctrl G, and chose "Special", "Blanks".

Then I clicked in the formula bar and typed =IF(E3=E4,D3,"N/A") (my first "aaa" is located in "D3") and pressed Ctrl ENTER, here are some screenshots:

Have fun :-)