I created this code for one of my reports, what I wanted is that the code should check for each cell in a specific column if they are empty or not. If they are empty, nothing to do but if the cells are not empty, it puts the word “done” (I can put other word, a date, etc.) for each corresponding cells in another column. Moreover, I put another code doing something similar, this one will copy only the value of a cell to put the same to new empty cells.

You may wondering what for the second code ? It is a kind of update because I was using the same report, and each time I put additional data in the specific column, I wanted to check in thisanother column the empty ones and to fill them with the same value.

When I use the macro ?

To fill with a value a column only when another column has not empty cells.

How to create the macro ?

Read How to create, edit, hide and select a macro in an excel report

How to create the button to associate it with the macro ?

Read How to create a button and associated it to a macro in an excel report

How is the macro ?

Copy the code below and paste it into your macro. You will see my comments in green if exist so follow the help to adapt to your need. This code checks if cells are not empty for a particular column, if it is true, it put “done” for each cell of another column.

Sub test()
Dim i As Integer
' starting to check from row 2
i = 2
With ActiveSheet
' change the column number 1 (column A) by the column number you want to check if the cells are not empty
' change the number 20 by how much rows you want to check
Do While (Cells(i, 1).Value <> "") And (i < 20)
' change the column number 2 (column B) by the column number you want to put done
' change done by a word you want
Cells(i, 2) = "done"
i = i + 1
Loop
End With
End Sub

This one checks if cells are not empty for a particular column, if it is true, it will check another column for empty cells and for all empty cells, it will copy/paste the value of a cell.

Sub test()
Dim i As Integer
' starting to check from row 2
i = 2
With ActiveSheet
' change the column number 1 (column A) by the column number you want to check if the cells are not empty
' change the number 20 by how much rows you want to check
Do While (Cells(i, 1).Value <> "") And (i < 20)
' change the column number 2 (column B) by the column number you want to check the empty cells
If IsEmpty(Cells(i, 2)) Then
' change B2 by the cell to copy and change the column number 2 (column B) by the column number to paste, it should be the same as the line above
Range("B2").Copy Cells(i, 2)
End If
i = i + 1
Loop
End With
End Sub