CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  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

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  3. #3
    Join Date
    Mar 2013
    Posts
    2

    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

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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
  •  





Click Here to Expand Forum to Full Width

Featured