Cannot jump to "ActiveCell' because it is hidden error

[liferg]

I am really hoping that someone can help with me my problem. I am not excel savvy with macro, but I can record a macro and get it to do what I need to do. I keep getting this message when I run my macro, click debug and then right click on the yellow highlighted error. A pivot table has been created based off of some information on another tab, however outside out the table I want to include a formula. This works on mine, but when I have copied this macro over to someone else this is where it freaks out at. I am not sure how much of the macro to copy and hope that I am copying enough for someone to assist me.

Sheets("Sheet9").Select</SPAN>
Cells(3, 1).Select</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PO#")</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 1</SPAN>
End With</SPAN>
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _</SPAN>
"PivotTable1").PivotFields("TCSTDIF1"), "Sum of TCSTDIF1", xlSum</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("IOSUPR")</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("IOSUPR").Subtotals = Array( _</SPAN>
False, False, False, False, False, False, False, False, False, False, False, False)</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("IOSUPR").LayoutForm = _</SPAN>
xlTabular</SPAN>
With ActiveSheet.PivotTables("PivotTable1").PivotFields("ISVNNO")</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 2</SPAN>
End With</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("ISVNNO").Subtotals = Array( _</SPAN>
False, False, False, False, False, False, False, False, False, False, False, False)</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("ISVNNO").LayoutForm = _</SPAN>
xlTabular</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("PO#").Subtotals = Array( _</SPAN>
False, False, False, False, False, False, False, False, False, False, False, False)</SPAN>
ActiveSheet.PivotTables("PivotTable1").PivotFields("PO#").LayoutForm = _</SPAN>
xlTabular</SPAN>
.Orientation = xlRowField</SPAN>
.Position = 2</SPAN>
Range("E4").Select</SPAN>
ActiveCell.FormulaR1C1 = _</SPAN></SPAN>
"=IF(AND(RC[-3]<>RC[-2],RC[-1]=0),""CHECK PO MANUALLY(BILLED PER PO)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>-100,RC[-1]<100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(CB@PE)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>100),""CHECK PO MANUALLY(BILLED LESS)"",IF(RC[-1]=0,""BILLED PER PO"",IF(AND(RC[-1]>-100,RC[-1]<100),""UNDER TOL"",IF(RC[-1]<-100,""CB@PE"",IF(RC[-1]>100,""BILLED LESS"","""")))))))))"</SPAN></SPAN>
Range("E4").Select</SPAN>
Selection.AutoFill Destination:=Range("E4:E1700")</SPAN>

 

[Andrew Poulsom]

Welcome to MrExcel.

What version of Excel do you have? There is a limit of 7 nested IFs in Excel 2003 and below and your formula has 8.

 

[liferg]

I have 2007. The formula has actually been working just fine on mine, but when I copy it over to someone else it doesn't work.

 

[Andrew Poulsom]

What version of Excel does 'someone else' have?

 

[liferg]

The other person also has 2007.

 

[Andrew Poulsom]

Does it work if you avoid Selecting?

Range("E4").FormulaR1C1 = _</SPAN></SPAN>
    "=IF(AND(RC[-3]<>RC[-2],RC[-1]=0),""CHECK PO MANUALLY(BILLED PER PO)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>-100,RC[-1]<100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(CB@PE)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>100),""CHECK PO MANUALLY(BILLED LESS)"",IF(RC[-1]=0,""BILLED PER PO"",IF(AND(RC[-1]>-100,RC[-1]<100),""UNDER TOL"",IF(RC[-1]<-100,""CB@PE"",IF(RC[-1]>100,""BILLED LESS"","""")))))))))"</SPAN></SPAN>

 

[liferg]

Hi Andrew,

We tried this and it still promps the same error. Am I missing something maybe in the previous coding leading up to this?

 

[Andrew Poulsom]

Which line is actually causing the error?

 

[liferg]

This is the part that continues to highlight:

ActiveCell.FormulaR1C1 = _</SPAN></SPAN>
"=IF(AND(RC[-3]<>RC[-2],RC[-1]=0),""CHECK PO MANUALLY(BILLED PER PO)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>-100,RC[-1]<100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(UNDER TOL)"",IF(AND(RC[-3]<>RC[-2],RC[-1]<-100),""CHECK PO MANUALLY(CB@PE)"",IF(AND(RC[-3]<>RC[-2],RC[-1]>100),""CHECK PO MANUALLY(BILLED LESS)"",IF(RC[-1]=0,""BILLED PER PO"",IF(AND(RC[-1]>-100,RC[-1]<100),""UNDER TOL"",IF(RC[-1]<-100,""CB@PE"",IF(RC[-1]>100,""BILLED LESS"","""")))))))))"</SPAN></SPAN>

 

[Andrew Poulsom]

I didn't use ActiveCell in the code I posted. That said I can't reproduce the error, which is described here:

XL2000: "Cannot jump to 'Application' because it is hidden."