CODEWITHSUNDEEP
vbaexcel
Coding Novice
Coding Novice

Reputation: 447

If Cell is Empty Copy Cell Above

Sub MacroFillAreas()

    For Each area In Columns("B:C").SpecialCells(xlCellTypeBlanks)
        If area.Cells.Row <= ActiveSheet.UsedRange.Rows.Count Then
            area.Cells = Range(area.Address).Offset(-1, 0).Value
        End If
    Next area
End Sub

This is my macro for filling blank cells in my workbook. I am throwing an object error on area.Cells = Range(area.Address).Offset(-1, 0).Value

Can anyone explain why this happens? Here is a quick example of the data and what I am trying to do with it.

A  1  12
A  1  12
A  
A  
A  2  14
A  
A  3  14
A
A

That has to copy down the cells above:

A  1  12 
A  1  12
A  1  12
A  1  12
A  2  14
A  2  14
A  3  14
A  3  14
A  3  14

Upvotes: 0

Views: 6520

Answers (1)

user6432984
user6432984

Reputation:

BruceWayneis right you'll get the error when B1:C1 are blank.

  • Range objects of non-continuous cells have multiple areas. For this reason you should rename area to cell for clarification.
  • area.Cells is misleading area is just 1 cell use area
  • area is a range no need for this Range(area.Address).Offset(-1, 0).Value use `area.Offset(-1, 0).Value instead
  • SpecialCells will throw an error if no cells are found. You need to trap this error.
Sub MacroFillAreas()

    Dim cell As Range, SearchRange As Range

    On Error Resume Next
    Set SearchRange = Columns("B:C").SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0

    If Not SearchRange Is Nothing Then
        For Each cell In SearchRange

            If cell.Row > 1 Then cell = cell.Offset(-1, 0).Value

        Next cell
    End If

End Sub

Upvotes: 2

Related Questions