+ Reply to Thread
Results 1 to 3 of 3

Code error “wrong number of arguments or invalid property assignment”

Hybrid View

hattrick_123a Code error “wrong number of... 09-19-2011, 12:02 AM
MarvinP Re: Code error “wrong number... 09-19-2011, 12:29 AM
hattrick_123a Re: Code error “wrong number... 09-19-2011, 06:16 PM
  1. #1
    Registered User
    Join Date
    04-28-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2003
    Posts
    24

    Code error “wrong number of arguments or invalid property assignment”

    What is wrong with this bit of code?

    Sub ShowAllItemsks()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim objPT As PivotTable
    Dim objPTField As PivotField
    Dim objPTItem As PivotItem
     
    For Each ws In Worksheets
    ws.Visible = xlSheetVisible
        For Each objPT In ws.PivotTables
     MsgBox "Pivot table name: " & objPT.Name & vbCrLf & "Sheet name: " & ws.Name & vbCrLf & "test"
        With objPT
                    
                    ' Run through all the column fields
            For Each objPTField In .ColumnFields
                        '
                        ' Get the hidden items and make them visible
                        For Each objPTItem In objPTField
                        
                        objPTItem("A").Visible = True
                        objPTItem("B").Visible = False
                        Next 'objPTItem
            Next 'objPTField
        End With
    Application.ScreenUpdating = True
     
        Next objPT
    Next ws
    End Sub
    It points at an error in this line of code with error “wrong number of arguments or invalid property assignment”

    objPTItem("A").Visible = True
    Appreciate any help. Example attached.
    Attached Files Attached Files

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,245

    Re: Code error “wrong number of arguments or invalid property assignment”

    Hey,
    In the immediate window this line gets to "A"
    ?application.ActiveSheet.pivottables(1).pivotfields(2).pivotitems(1).name
    Could it be you are looking for "A" in pivotfields(1) which is the Date? This might cause the error as dates and text are different?

    Also does .ColumnsFields work? ColumnsFields is really PivotFields(2) ?

    Also - in looking for a similar problem from the past, find the attached which may help.
    Attached Files Attached Files
    Last edited by MarvinP; 09-19-2011 at 12:38 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    04-28-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2003
    Posts
    24

    Re: Code error “wrong number of arguments or invalid property assignment”

    Thanks MarvinP

    I got this to do what I want using the below

    Sub Macro8()
    '
    ' Macro5 Macro  TO WORK ON THIS
    '
    
    '
    Dim ws As Worksheet
    Dim objPT As PivotTable
    Dim objPTField As PivotField
    Dim PvName As String
    PvName = "NE"
    
    For Each ws In Worksheets
    ws.Visible = xlSheetVisible
        
        For Each objPT In ws.PivotTables
            With objPT.PivotFields("NE")
                .PivotItems("A").Visible = True
                .PivotItems("B").Visible = False
            
            End With
        Next objPT
    Next ws
    End Sub

    but i would like to build on this and have something like the below but I keep getting an error “wrong number of arguments or invalid property assignment”. So I thought it might be a syntax issue? The code below I believe is possibly better it that I am trying to use Variables, which would be better long term for the code


    Sub Macro10()
    '
    ' Macro5 Macro  TO WORK ON THIS
    '
    
    '
    Dim ws As Worksheet
    Dim objPT As PivotTable
    Dim objPTField As PivotField
    Dim objPTItem As PivotItem
    
    Dim PvName As String
    PvName = "NE"
    
    For Each ws In Worksheets
    ws.Visible = xlSheetVisible
        
        For Each objPT In ws.PivotTables
            With objPT.objPTItem("NE")
                objPTItem("B").Visible = True
                objPTItem("A").Visible = False
            
            End With
        Next objPT
    Next ws
    End Sub
    Could it be you are looking for "A" in pivotfields(1) which is the Date?
    I have fixed this now

    This might cause the error as dates and text are different?
    I am not looking at dates here, but I will be in another MACRO that I am working on

    Also does .ColumnsFields work? ColumnsFields is really PivotFields(2) ?
    I have fixed this now

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1