Home > database >  Update an Empty Cell in a range
Update an Empty Cell in a range

Time:03-16

I'm looking to update a cell on a sheet when it's left empty. If there is data in column B but not in column AA, I need to insert something into column AA.

I have made the following code but have failed to make it update the cell:

Range("B2").Select
        Do Until IsEmpty(ActiveCell)
            Dim LoopRowNo As Integer
            LoopRowNo = ActiveCell.Row
            
            If IsEmpty(Range(Cells(LoopRowNo, 26))) Then Range(Cells(LoopRowNo, 26)).Value = "01/01/1990"
            ActiveCell.Offset(1, 0).Select
        Loop

Hoping someone can point me in the right direction.

CodePudding user response:

  • Use Range or Cells, but not both.
  • Don't Select.
With ActiveSheet
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row

    Dim i As Long
    For i = 2 to lastRow
        If IsEmpty(.Cells(i, "AA")) And Not IsEmpty(.Cells(i, "B")) Then
            .Cells(i, "AA").Value = "01/01/1990"
        End If
    Next
End With
  • Related