Application-defined or object error 1004

[MarkCBB]

Hi there VBA Pros,


I am getting the following error when try the run this code (Application-defined or object error 1004)

Sub Filter_PayRoll_Report_PivotTables()
Dim MonthYear_Name As Range

Set MonthYear_Name = Sheet3.Range("E2")

With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlManual
End With
    With Sheet2.PivotTables("PivotTable2").PivotFields("MONTHYEAR")
        .PivotItems(MonthYear_Name.Value).Visible = True
        For Each Pi In .PivotItems
           If Pi.Name <> MonthYear_Name.Value Then Pi.Visible = False
         Next Pi
    End With
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
'Need to fix this to be dynamic
With Sheet2
'The error is the below line of code
    .Range("C5:AA35").FormulaR1C1 = "=IF(R4C="""","""",IF((RC2-0)>TODAY(),0,IF(RC2="""","""",IF(AND(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)=0,OR(RC1=""SATURDAY"",RC1=""SUNDAY"")),0,IF(AND((RC2-0)>=VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE),VLOOKUP(R4C,DB_AGENTS!C1:C6,4,FALSE)=""TEAM LEADER"",VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)<>0),150,IF(AND(VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)=0,VLOOKUP(R4C,DB_AGENTS!C1:C6)=""TEAM LEADER""),150,IF(AND(VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)=0,VLOOKUP(R4C,DB_AGENTS!C1:C6,4,FALSE)=""AGENT""),100,100))))+IF(AND(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)=0,OR(RC1=""Saturday"",RC1=""Sunday"")),0,IF(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)<CALCULATIONS!R2C9,-(CALCULATIONS!R2C9-COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2))*5,IF(COUNTIFS(DB_OPS!C10,R4C,9,RC2)<=CALCULATIONS!R3C9,COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)*1.5))))))"
    .Range("C5:AA35").Copy
    .Range("C5:AA35").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

End Sub

I have a feeling the formula that I am using is a bit long.

 

[MarkCBB]

Me again,

The only way I can think of doing this is to paste the formula into the range from another range (i.e. like a storge range)

 

[John Davis]

I noticed from your post your formula is missing the last quotation mark at the end )".

 

[MarkCBB]

Appologies the quotation mark was meant to be there and is there in my Code. I have tested a few things on this to see why it doesnt work. i.e. I have started the Macro recorder, selected the cell with the formula and hit F2 and enter. and I tried to play the macro only to get the same error.

The only way I have found to get around this is to paste the formula in a cell away from the database it needs to populate and use the code to copy that cell and paste the formula into the datatable range.

 

[AlphaFrog]

One of the VLOOKUPs is missing two arguments

There seems to be missing some parentheses at the end of the seven nested IFs

It may help to visualize the formula if you break it down in to several lines.

.Range("C5:AA35").FormulaR1C1 = "=IF(R4C="""",""""," & _
                                 "IF((RC2-0)>TODAY(),0," & _
                                 "IF(RC2="""",""""," & _
                                 "IF(AND(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)=0,OR(RC1=""SATURDAY"",RC1=""SUNDAY"")),0," & _
                                 "IF(AND((RC2-0)>=VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE),VLOOKUP(R4C,DB_AGENTS!C1:C6,4,FALSE)=""TEAM LEADER"",VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)<>0),150," & _
                                 "IF(AND(VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)=0,VLOOKUP(R4C,DB_AGENTS!C1:C6[COLOR="Red"],4,FALSE[/COLOR])=""TEAM LEADER""),150," & _
                                 "IF(AND(VLOOKUP(R4C,DB_AGENTS!C1:C6,6,FALSE)=0,VLOOKUP(R4C,DB_AGENTS!C1:C6,4,FALSE)=""AGENT"")[COLOR="Blue"],100,100[/COLOR]))))[COLOR="Red"])))[/COLOR]" & _
                                    "+IF(AND(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)=0,OR(RC1=""Saturday"",RC1=""Sunday"")),0,IF(COUNTIFS(DB_OPS!C10,R4C,DB_OPS!C29,RC2)[COLOR="Blue"],TRUE?, FALSE?[/COLOR])"

This still doesn't work but I could not figure out what you wanted to do.
The last of the seven nested IFs returns 100 for both TRUE and FALSE. That didn't seem to make sense.
The last line with the last IF doesn't seem right. It's missing the True\False arguments.

It may be that some of your formula got truncated.

 

[MarkCBB]

HI there AlphaFrog,

Yup it looks like the formula didnt come out correctly when I pasted it into the message box.

Hopefully the below one does. The Formula is working the way I need it too, my challenge is that this formula is in over 1000 cells, thus making the workbook really slow, once I calculated the formula I value the results as values. however I have stored this formula in A2 and paste it into the other range. I need to be able to have this formula in the code,and not move it from a cell.

=IF(E$4="","",IF(($B25-0)>TODAY(),0,IF($B25="","",IF(AND(COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25)=0,OR($A25="SATURDAY",$A25="SUNDAY")),0,IF(AND(($B25-0)>=VLOOKUP(E$4,DB_AGENTS!$A:$F,6,FALSE),VLOOKUP(E$4,DB_AGENTS!$A:$F,4,FALSE)="TEAM LEADER",VLOOKUP(E$4,DB_AGENTS!$A:$F,6,FALSE)<>0),150,IF(AND(VLOOKUP(E$4,DB_AGENTS!$A:$F,6,FALSE)=0,VLOOKUP(E$4,DB_AGENTS!$A:$F,4,FALSE)="TEAM LEADER"),150,IF(AND(VLOOKUP(E$4,DB_AGENTS!$A:$F,6,FALSE)=0,VLOOKUP(E$4,DB_AGENTS!$A:$F,4,FALSE)="AGENT"),100,100))))+IF(AND(COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25)=0,OR($A25="Saturday",$A25="Sunday")),0,IF(COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25)<CALCULATIONS!$I$2,-(CALCULATIONS!$I$2-COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25))*5,IF(COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25)<=CALCULATIONS!$I$3,COUNTIFS(DB_OPS!$J:$J,E$4,DB_OPS!$AC:$AC,$B25)*1.5))))))

 

[mikerickson]

An alternative to pasting a huge long formula into that many cells would be to define a named formula and have the macro put =MyNamedFormula in each of the 1000 cells.

This will not improve the performance, but it might cure the problem that you are having with the code erroring.

 

[AlphaFrog]

Put your working formula in a cell
Go to the Excel Options dialog and change to the R1C1 reference style
Your formula in the cell will now be in the R1C1 reference slyle. You can copy it and use it in VBA.
You'll still have to edit it slightly, but it should be close to what you need.
You can change the R1C1 reference style option back to normal.

 

[mikerickson]

Another way to get the VB code for the formula would be to put the working formula in a cell.
Press Record a macro.
Select the cell, put the cursor in the formula window, delete one character, re-type that character, press enter.
Press Stop recording.

The formula (in R1C1 format) with all the parenthesis doubled etc is in the recorded routine.