Excel VBA: Run-time error '1004' Application-defined or object-defined error

Question

I have written a piece of code for a checklist that we use at my place of work - this has been working for 6 months with no issues and now suddenly I am getting a "Run-time error '1004' Application-defined or object-defined error" caused by this line:

Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"

The sheets I use are protected but as you can see they are unprotected at the start of the macro. The code is placed in a module so this shouldn't be the issue either. I have also tried specifying the worksheet before each range but this isn't working for me either (E.g., Ov.Range("U" & ar)....).

Everything works fine up until that line and for the life of me I can't figure out whats causing the error. Any help you can provide is appreciated!

Sub InsertRows()

Dim Ov, Bo, Bi, SU, Co, PF, ws As Worksheet
Dim ar As Long
Dim ce As Range
Set Ov = Sheets("Overview")
Set Bo = Sheets("Booking")
Set Bi = Sheets("Billing")
Set SU = Sheets("Set Up")
Set Co = Sheets("Copy")
Set PF = Sheets("PCA & Feedback")

ar = ActiveCell.Row

Application.ScreenUpdating = False

For Each ws In Worksheets
ws.Unprotect
Next

Bo.Activate
Rows(ar & ":" & ar).EntireRow.Insert
Range("E" & ar).Formula = "=VLOOKUP(C" & ar & ",'Deal Numbers 2015'!$A$2:$B$85,2,FALSE)"
Range("Q" & ar).Formula = "=VLOOKUP(H" & ar & ", Lists!$A$18:$B$25, 2, FALSE)"
Range("R" & ar).Formula = "=WORKDAY(I" & ar & ", -Q" & ar & ")"

Bi.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "=Booking!P" & ar
Range("H" & ar).Formula = "=Booking!F" & ar
Range("I" & ar).Formula = "=Booking!G" & ar
Range("M" & ar).Formula = "=IF(AND(L" & ar & "=""Y"", N" & ar & " = """"), ""Y"", ""N"")"

SU.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "=Booking!P" & ar
Range("L" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -8)"
Range("N" & ar).Formula = "=IF(AND(COUNTA(O" & ar & ")=1, ISBLANK(M" & ar & ")), ""Y"", ""N"")"

Co.Activate
Rows(ar & ":" & ar).EntireRow.Insert

Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!I" & ar
Range("F" & ar).Formula = "=Booking!J" & ar
Range("G" & ar).Formula = "='Set Up'!K" & ar
Range("H" & ar).Formula = "='Set Up'!O" & ar
Range("J" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -7)"
Range("L" & ar).Formula = "=IF(AND(ISNUMBER(SEARCH(""Copy Attached"", M" & ar & ")), ISBLANK(K" & ar & ")), ""Y"", ""N"")"
Range("N" & ar).Formula = "=IF(AND(M" & ar & "=""Copy Attached"", OR(O" & ar & "=""N"", O" & ar & " = """")), ""Y"", ""N"")"

Range("I" & ar).Value = "Awaiting Set Up"
Range("M" & ar).Value = "Copy Not Attached"

PF.Activate
Rows(ar & ":" & ar).EntireRow.Insert
Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=Booking!D" & ar
Range("E" & ar).Formula = "=Booking!J" & ar
Range("F" & ar).Formula = "=WORKDAY(Booking!J" & ar & ", 8)"
Range("H" & ar).Formula = "=IF(AND(G" & ar & "=""Y"", I" & ar & "=""""), ""Y"", ""N"")"
Range("M" & ar).Formula = "=WORKDAY(F" & ar & ", 10)"
Range("P" & ar).Formula = "=WORKDAY(O" & ar & ", 10)"

  'Adding Row to Sheet
Ov.Activate

Rows(ar & ":" & ar).EntireRow.Insert

'Adding formulas to Sheet
Range("B" & ar).Formula = "=Booking!B" & ar
Range("C" & ar).Formula = "=Booking!C" & ar
Range("D" & ar).Formula = "=IF(Booking!D" & ar & "=0, """", Booking!D" & ar & "&""-""&Booking!K" & ar & ")"
Range("E" & ar).Formula = "=Booking!F" & ar
Range("F" & ar).Formula = "=Booking!I" & ar
Range("G" & ar).Formula = "=Booking!J" & ar
Range("H" & ar).Formula = "=Booking!H" & ar
Range("I" & ar).Formula = "=ISBLANK(Booking!P" & ar & ")"
Range("J" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Booking!M" & ar & "=""Y"", ""On SF"", ""Not on SF""))"
Range("K" & ar).Formula = "=IF(D" & ar & "="""", """", IF(I" & ar & "=TRUE, J" & ar & ", Booking!P" & ar & "))"
Range("L" & ar).Formula = "=ISBLANK('Set Up'!K" & ar & ")"
Range("M" & ar).Formula = "=Booking!R" & ar
Range("N" & ar).Formula = "=IF(Booking!G" & ar & "=""Y"", 1, 0)"
Range("O" & ar).Formula = "=IF(Booking!N" & ar & "=""Closed Won"", 1, 0)"
Range("P" & ar).Formula = "=IF(D" & ar & "="""", """", IF(SUM(N" & ar & ":O" & ar & ")<2, ""N"", IF(SUM(N" & ar & ":O" & ar & ")=2, ""Y"")))"
Range("Q" & ar).Formula = "=IF(D" & ar & "="""", """", IF(L" & ar & "=TRUE, ""Requested"", 'Set Up'!K" & ar & "))"
Range("R" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Copy!M" & ar & "=""Copy Not Attached"", Copy!I" & ar & ", Copy!M" & ar & "))"
Range("S" & ar).Formula = "=Copy!J" & ar
Range("T" & ar).Formula = "='PCA & Feedback'!I" & ar
Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"
Range("V" & ar).Formula = "=IF(COUNTBLANK(Booking!S" & ar & ")+COUNTBLANK('Set Up'!R" & ar & ")+COUNTBLANK(Copy!P" & ar & ")=3, """", Booking!S" & ar & "&""; ""&'Set Up'!R" & ar & "&""; ""&Copy!P" & ar & ")"

For Each ws In Worksheets
ws.Protect , DrawingObjects:=False, AllowFiltering:=True
Next

Bo.Activate
Range("B" & ar).Activate

Application.ScreenUpdating = True

End Sub

Ideally, you should be using your worksheet "anchors" for all your range access, i.e. Ov.Range.... Alternatively, you can wrap all your range statements with a simple With Ov ... End With, which would be more efficient. As to your error, especially if all the other Range.Formula = ... statements are working, try changing the line that errors with a dummy formula, such as Range("U" & ar).Formula = "=""hello world""" and see if that gives you an error too. It will help to bracket where the exact problem is. — PeterT
I don't know the answer but I am suspicious with name like this 'PCA & Feedback'!M" with special characters. These are often errors waiting to happen. Does the error happen all the time independent of the data? or is there only and error with some data? — Edgar
Remember to declare Ov, Bo, Bi, SU, Co & PF as worksheets. At the moment they're variants - only ws is a worksheet. — Darren Bartrup-Cook

Darren Bartrup-Cook Darren Bartrup-Cook · Accepted Answer · 2018-08-15T13:04:27

Your specific problem is that the formula is missing an ! at PCA & Feedback'R & ar

Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"

I've rewritten the code to include With and removing the need to activate each sheet.
I also commented out the screen updating - shouldn't really need it as the sheets aren't activated anymore.

Note 1: where I have written ThisWorkbook I'm referring to the workbook that the code is in.
Note 2: When using a cell reference within a With...End With block start the reference with ..
So .Range("E" & ar) rather than just Range("E" & ar)

Sub InsertRows()

    Dim Ov As Worksheet, Bo As Worksheet, Bi As Worksheet, SU As Worksheet
    Dim Co As Worksheet, PF As Worksheet, ws As Worksheet
    Dim ar As Long
    Dim ce As Range

    With ThisWorkbook
        Set Ov = .Sheets("Overview")
        Set Bo = .Sheets("Booking")
        Set Bi = .Sheets("Billing")
        Set SU = .Sheets("Set Up")
        Set Co = .Sheets("Copy")
        Set PF = .Sheets("PCA & Feedback")
    End With

    ar = ActiveCell.Row

    'Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
        ws.Unprotect
    Next

    'Bo.Activate
    With Bo
        .Rows(ar & ":" & ar).EntireRow.Insert
        .Range("E" & ar).Formula = "=VLOOKUP(C" & ar & ",'Deal Numbers 2015'!$A$2:$B$85,2,FALSE)"
        .Range("Q" & ar).Formula = "=VLOOKUP(H" & ar & ", Lists!$A$18:$B$25, 2, FALSE)"
        .Range("R" & ar).Formula = "=WORKDAY(I" & ar & ", -Q" & ar & ")"
    End With

    'Bi.Activate
    With Bi
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "=Booking!P" & ar
        .Range("H" & ar).Formula = "=Booking!F" & ar
        .Range("I" & ar).Formula = "=Booking!G" & ar
        .Range("M" & ar).Formula = "=IF(AND(L" & ar & "=""Y"", N" & ar & " = """"), ""Y"", ""N"")"
    End With

    'SU.Activate
    With SU
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "=Booking!P" & ar
        .Range("L" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -8)"
        .Range("N" & ar).Formula = "=IF(AND(COUNTA(O" & ar & ")=1, ISBLANK(M" & ar & ")), ""Y"", ""N"")"
    End With

    'Co.Activate
    With Co
        .Rows(ar & ":" & ar).EntireRow.Insert

        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!I" & ar
        .Range("F" & ar).Formula = "=Booking!J" & ar
        .Range("G" & ar).Formula = "='Set Up'!K" & ar
        .Range("H" & ar).Formula = "='Set Up'!O" & ar
        .Range("J" & ar).Formula = "=WORKDAY(Booking!I" & ar & ", -7)"
        .Range("L" & ar).Formula = "=IF(AND(ISNUMBER(SEARCH(""Copy Attached"", M" & ar & ")), ISBLANK(K" & ar & ")), ""Y"", ""N"")"
        .Range("N" & ar).Formula = "=IF(AND(M" & ar & "=""Copy Attached"", OR(O" & ar & "=""N"", O" & ar & " = """")), ""Y"", ""N"")"

        .Range("I" & ar).Value = "Awaiting Set Up"
        .Range("M" & ar).Value = "Copy Not Attached"
    End With

    'PF.Activate
    With PF
        .Rows(ar & ":" & ar).EntireRow.Insert
        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=Booking!D" & ar
        .Range("E" & ar).Formula = "=Booking!J" & ar
        .Range("F" & ar).Formula = "=WORKDAY(Booking!J" & ar & ", 8)"
        .Range("H" & ar).Formula = "=IF(AND(G" & ar & "=""Y"", I" & ar & "=""""), ""Y"", ""N"")"
        .Range("M" & ar).Formula = "=WORKDAY(F" & ar & ", 10)"
        .Range("P" & ar).Formula = "=WORKDAY(O" & ar & ", 10)"
    End With

      'Adding Row to Sheet
    'Ov.Activate

    With Ov

        .Rows(ar & ":" & ar).EntireRow.Insert

        'Adding formulas to Sheet
        .Range("B" & ar).Formula = "=Booking!B" & ar
        .Range("C" & ar).Formula = "=Booking!C" & ar
        .Range("D" & ar).Formula = "=IF(Booking!D" & ar & "=0, """", Booking!D" & ar & "&""-""&Booking!K" & ar & ")"
        .Range("E" & ar).Formula = "=Booking!F" & ar
        .Range("F" & ar).Formula = "=Booking!I" & ar
        .Range("G" & ar).Formula = "=Booking!J" & ar
        .Range("H" & ar).Formula = "=Booking!H" & ar
        .Range("I" & ar).Formula = "=ISBLANK(Booking!P" & ar & ")"
        .Range("J" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Booking!M" & ar & "=""Y"", ""On SF"", ""Not on SF""))"
        .Range("K" & ar).Formula = "=IF(D" & ar & "="""", """", IF(I" & ar & "=TRUE, J" & ar & ", Booking!P" & ar & "))"
        .Range("L" & ar).Formula = "=ISBLANK('Set Up'!K" & ar & ")"
        .Range("M" & ar).Formula = "=Booking!R" & ar
        .Range("N" & ar).Formula = "=IF(Booking!G" & ar & "=""Y"", 1, 0)"
        .Range("O" & ar).Formula = "=IF(Booking!N" & ar & "=""Closed Won"", 1, 0)"
        .Range("P" & ar).Formula = "=IF(D" & ar & "="""", """", IF(SUM(N" & ar & ":O" & ar & ")<2, ""N"", IF(SUM(N" & ar & ":O" & ar & ")=2, ""Y"")))"
        .Range("Q" & ar).Formula = "=IF(D" & ar & "="""", """", IF(L" & ar & "=TRUE, ""Requested"", 'Set Up'!K" & ar & "))"
        .Range("R" & ar).Formula = "=IF(D" & ar & "="""", """", IF(Copy!M" & ar & "=""Copy Not Attached"", Copy!I" & ar & ", Copy!M" & ar & "))"
        .Range("S" & ar).Formula = "=Copy!J" & ar
        .Range("T" & ar).Formula = "='PCA & Feedback'!I" & ar
        .Range("U" & ar).Formula = "=IF('PCA & Feedback'!Q" & ar & "=""Y"", 'PCA & Feedback'!R" & ar & ", IF('PCA & Feedback'!N" & ar & "="""", 'PCA & Feedback'!M" & ar & ", 'PCA & Feedback'!N" & ar & "))"
        .Range("V" & ar).Formula = "=IF(COUNTBLANK(Booking!S" & ar & ")+COUNTBLANK('Set Up'!R" & ar & ")+COUNTBLANK(Copy!P" & ar & ")=3, """", Booking!S" & ar & "&""; ""&'Set Up'!R" & ar & "&""; ""&Copy!P" & ar & ")"

    End With

    For Each ws In Worksheets
        ws.Protect , DrawingObjects:=False, AllowFiltering:=True
    Next

    Bo.Activate
    Bo.Range("B" & ar).Activate

    'Application.ScreenUpdating = True

End Sub

Excel VBA: Run-time error '1004' Application-defined or object-defined error

1 Answers