+ Reply to Thread
Results 1 to 12 of 12

Assign a variable to an object parameter

  1. #1
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Assign a variable to an object parameter

    I need to create a pivot table with vba. Using the macro recorder I understand the statement is:

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Sheet1!R1C1:R398C6", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet4!R3C1", TableName:="Tabella_pivot1", _
    DefaultVersion:=xlPivotTableVersion14


    The SourceData parameter specifies where the input data are. In the above case data are on sheet1 and the data range is row 1 col 1 to row 398 to col 6.
    I need to change this value according to what the user store in Sheet1. For this reason I have modified the above code in this way:


    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
    "Sheet1!R1C1:R" & Mid(Str(UR), 2) & "C6", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Pivot!R3C1", TableName:="Tabella_pivot1", _
    DefaultVersion:=xlPivotTableVersion14

    where UR is variable declared as Integer containing the last row range.

    Unfortunately, I get a run time error 5.

    Please, can somebody tell me where I am wrong? Thanks

  2. #2
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Assign a variable to an object parameter

    This works fine for me

    Please Login or Register  to view this content.

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Assign a variable to an object parameter

    Hi,

    Would you please note the forum rules and enclose code between code tags. I'll let it pass as this is your first post.

    Your description is somewhat ambiguous. You say that UR is an integer, but then go on to say it contains the last row range.
    Do you mean it contains the last row number? And in which case what is the reference to 'Str' in the Mid(Str(UR)...instruction.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Assign a variable to an object parameter

    I apologize. English is not my native language. I meant UR contains the number of the last row of the range representing the datasource for the pivot table.

    I tried with the source by Bob but it doesn't work. I am using Excel 2010.

  5. #5
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Assign a variable to an object parameter

    If UR actually contains a row number, then Bob's code should work fine.
    Remember what the dormouse said
    Feed your head

  6. #6
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Assign a variable to an object parameter

    Looking at the debug it really contains a number, but still I get a runtime error 5.

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Assign a variable to an object parameter

    Do you already have a pivot table on that sheet?

  8. #8
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Assign a variable to an object parameter

    No. Some code executed before the pivot creation delete the sheet (if existing) used to contain the pivot table. In any case I am debugging and I have checked the pivot table does not exist.

  9. #9
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Assign a variable to an object parameter

    Rather than us guessing then, can you post the whole code?

  10. #10
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Assign a variable to an object parameter

    Yes, Thank you. As you can see data come from access, but I think this is not important.


    Please Login or Register  to view this content.
    Comments are in Italian, my language

  11. #11
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Assign a variable to an object parameter

    You have deleted the Pivot sheet and then tried to specify it as the destination!

  12. #12
    Registered User
    Join Date
    01-07-2011
    Location
    Piacenza, Italy
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Assign a variable to an object parameter

    Can you believe it? I have been working for hours and I didn't see...

    However, now I get an 1004 error! I have to investigate.

+ 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