VBA:Find a cell, insert a new row below and copy data into it

[RonanH]

Hi all

I have a large list of data that has missing entries.

I have started a macro to look for an "ERROR" entry (I have a simple if statement to insert ERROR if there is data missing) and insert a row below it. I would also like to delete the word ERROR.

But I am having trouble copying data into the new row.

There are 5 relevant colums:

A: This stays the same
B: This stays the same
C: If the number is divisble by 20, add 30 otherwise add 70
D: Inser the average of the number above and below the new row.

this is what I have so far.

Sub Macro4()
'
' Macro4 Macro
'
'
    Cells.Find(What:="ERROR", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
        ActiveCell.Delete
        ActiveCell.Offset(1).EntireRow.Insert
 
        ActiveCell.Offset(0, -4).Resize(1, 4).Copy
        With ActiveCell
            .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
            .ClearOutline
        End With
End Sub

Many thanks,

Ro