VBA run time error 91 Object variable or with block variable not set

Question

Hi I am totally new to VBA. Currently I am learning how to use vba to create Pivottable and charts. But I am facing the Error 91 problem after debugged my code as below. I am trying to count the number of failpartid occurred at certain time, and then draw the pivottable to show the count number. Thank you!

P.S: So I edited and checked my code again, but found out the "PT is nothing" during debugging. I have dim PT at the beginning, does anybody know why?

    With .PivotFields("FailPartId") is the line has the error.

   Sub Trial1()
  Dim PTCache As PivotCaches
  Dim PT As PivotTables
  Dim PF As PivotFields
  Dim FinalCol As Long

    FinalCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
    'Range("E1:F1").Select
    'Range(Selection, Selection.End(xlDown)).Select
    Selection.Name = "PivotData"

    Application.Goto Reference:="Trial1"
    'Create the Cache
    Set PTCaches = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,       SourceData:="ActiveSheet!A1")

    'Create the pivot table
     Set PT = PTCaches.CreatePivotTable(TableDestination:=ActiveSheet.Cells(1, FinalCol   + 2), TableName:=”PivotTable1”, ReadData:="PivotData")
    'Set the Fields
    With PT
   'Set column field
    With .PivotFields("FailPartId")
    .Orientation = xlColumnField
    .Position = 1
   End With
 'Set Row field
     With .PivotFields("ProductionYM")
     .Orientation = xlRowField
     .Position = 1
    End With

 'Set data feild
     ActiveSheet.AddDataField.PivotFields ("FailPartId"), "Count of FailPartId", xlCount
     End With

    End Sub

Add this line MsgBox PT Is Nothing just before With PT . What you will get? — Dmitry Pavliv
– Dmitry Pavliv, Commented Jan 27, 2014 at 20:07
One problem is in the line a few above: Set PT = PTCaches.CreatePivotTable... You have never set PTCaches to anything prior to that line (at least not in the code you've provided). You declared it, but never set it. And in the line above that, you are setting an undeclared variable: PTCache, which is never referenced again in the code you've provided. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jan 27, 2014 at 20:26

PatricK · Accepted Answer · 2014-01-27 22:16:13Z

0

If you have put Option Explicit at the top and to Debug-->Compile VBAProject, you will solve it yourself.

Replace all PTCaches with PTCache and then change the line

Dim PTCache As PivotCaches to Dim PTCache As PivotCache.

Also the ” may become another issue. TableName:=”PivotTable1” is not the same as TableName:="PivotTable1"

enter image description here

answered Jan 27, 2014 at 22:16

PatricK

6,4231 gold badge23 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Solomon Over a year ago

Hey Thanks for pointing out! I tried but the same problem happened again.Is there any possibility that I set the data range wrong, then the code won't be able to create the PivotCache?

PatricK Over a year ago

can you step into the macro by selecting a line in the Sub, press <kbd>F8</kbd> and go through the code line by line? You should spot errors easier this way as you can change variables in real time.

Collectives™ on Stack Overflow

VBA run time error 91 Object variable or with block variable not set

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related