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>

 

[liferg]

<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">We used your code and it still highlights but does not provide me with an error. I am trying to attach a snap shot of the VBA screen shot, but this forum is not allowing me to do it.

</v:shapetype><v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600">Range("E4").FormulaR1C1 = _</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>
</v:shapetype><v:shapetype stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"></v:shapetype></SPAN>

 

[Andrew Poulsom]

What do you mean by highlights?

 

[liferg]

When we run the macro, prompts us with some options, so we usually select "debug". It then opens up VBA and when this opens it goes to the above code and it's highlighted in yellow. Normally I can right click on it and it tells me what the error is, but this time when I use the code you gave me, it is not giving me the error. But the code I referenced above is where it's getting stuck.

 

[Jonmo1]

There must be an error...

When we run the macro, prompts us with some options, so we usually select "debug".

On that prompt, before you select Debug, there must be 'something' written in that dialogue window.

 

[Andrew Poulsom]

What's the text in the message box that gives you some options?

 

[liferg]

The only error that prompts before that is:

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

And this is where we select "debug"

 

[Andrew Poulsom]

That means there is something wrong with your formula. The most likely cause is that they have a version of Excel prior to Excel 2007 (as I said at the outset).

 

[Jonmo1]

Or even if they have xl2007+, they could be opening it in compatibility mode.

Add this line to the code, and what is the message box result - when it is run on the problematic machine.

MsgBox Rows.Count

 

[liferg]

Do I put this code before or the Range or after the formula?

 

[liferg]

I put the code before the range and we get the following message:

Compile error: Invalid qualifier

I also noticed that when he opens his excel, he gets a message that says his personal file is locked for editing, and it's not in use. Could this be the reason for the original error message that I was receiving?