Run-time error '424' Object required
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: Run-time error '424' Object required

Threaded View

  1. #1
    Join Date
    Mar 2013
    Posts
    2

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center