-
March 13th, 2013, 05:56 AM
#1
Run-time error '424' Object required
Hi
I have written a Macro code to add certain column and formula and to add new sheet and in that new sheet a pivot table. When I try to run everything works well and gets stuck when creating a pivot table. I am getting Run Time error 424 "object required".
Below is macro code:
Code:
Sub VisaResolvedCasesMacro1()
Dim Pt As PivotTable
Dim cacheofPt As PivotCache
Dim Pf As PivotField
Dim Pi As PivotItem
ActiveCell.Offset(0, 4).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Cycle Time"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
ActiveCell.Select
Selection.NumberFormat = "0.00"
Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
ActiveCell.Range("A1:A391").Select
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Network Days"
ActiveCell.Offset(0, -1).Columns("A:B").EntireColumn.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=NETWORKDAYS(RC[-3],RC[-2])-1-MOD(RC[-3],1)+MOD(RC[-2],1)"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
ActiveCell.Range("A1:A391").Select
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Age Group"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]<=1, ""<=24H"",IF(RC[-1]<=2, ""24-48H"",IF(RC[-1]<=5, ""2-5 D"",IF(RC[-1]>5, ""Over 5D""))))"
ActiveCell.Select
Selection.AutoFill Destination:=ActiveCell.Range("A1:A391")
ActiveCell.Range("A1:A391").Select
ActiveCell.Offset(-1, 25).Range("A1").Select
ActiveCell.Offset(0, -8).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -9).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -6).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -5).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(0, -4).Columns("A:A").EntireColumn.EntireColumn.AutoFit
ActiveCell.Offset(4, -8).Range("A1").Select
Cells.Replace What:="5-Excellent", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="4-Above Average", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="3-Met Needs", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="2-Below Average", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Cells.Replace What:="1-Unacceptable", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets.Add
Sheets("Sheet1").Select
Set cacheofPt = ActiveWorkbook.PivotCaches.Create(xlDatabase, Range("A1:AF1000"))
Sheets("Sheet1").Select
Set Pt = ActiveSheet.PivotTables.Add(cacheofPt, Range("A1"), "MyPt")
With Pt
.PivotFields("Case Sub Area 1").Orientation = xl.PageField
.PivotFields("Case Division").Orientation = xl.RowField
.PivotFields("Case Number").Orientation = xl.DataField
.Caption = "Count of Case Numbers"
.Function = xlCount
.PivotFields("Cycle Time").Orientation = xl.DataField
.Caption = "Avg of Cycle Time"
.Function = xlAverage
.NumberFormat = "0.0"
.PivotFields("Question 5").Orientation = xl.DataField
.Caption = "Avg. of Question 5"
.Function = xlAverage
.NumberFormat = "0.0"
End With
End Sub
Last edited by DataMiser; March 13th, 2013 at 01:02 PM.
Reason: added code tags
-
March 13th, 2013, 11:41 AM
#2
Re: Run-time error '424' Object required
Where does the error occur? This error typically means that you are referencing something that is not present in the current scope. Can also mean you have a typo in an object name. Locate the line where the error occurs and check each object reference on that line and you will locate your problem.
When posting code always use [code][/code] tags to retain formatting for readability and always indicate where the error occurs.
Always use [code][/code] tags when posting code.
-
March 13th, 2013, 12:03 PM
#3
Re: Run-time error '424' Object required
Thank you for replying.
Error occurs in the below mentioned line
" .PivotFields("Case Sub Area 1").Orientation = xl.PageField "
I am new to this macro coding. I learnt this macro coding watching the video from Excelvbasql.com website.
Regards
Chetan
-
March 13th, 2013, 01:05 PM
#4
Re: Run-time error '424' Object required
I do not work with Excel Macros so I can't be sure but this part looks like the likely culprit
= xl.PageField
I see no place in the code where xl is assigned to anything so am guess that is the object it is referring to if so then the same error would happen on the next few lines that follow as well.
Always use [code][/code] tags when posting code.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|