+ Reply to Thread
Results 1 to 8 of 8

"Wrong number of arguments or invalid property assignment" *sigh*

  1. #1
    Registered User
    Join Date
    05-16-2012
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    31

    "Wrong number of arguments or invalid property assignment" *sigh*

    I'm trying to get a userform to (depending on certain values) populate different worksheets and ranges in those worksheets.
    Below is the code i'm working with (in a module) while the information needing to be inserted is coming from Userform1.

    However, I'm getting 2 errors, the first is with
    Please Login or Register  to view this content.
    and I get the "Wrong number of arguments or invalid property assignment"

    The second is I get a "subscript out of range" with the
    Please Login or Register  to view this content.
    and down....

    any idea why it isn't working or what i need to tweak?
    below is the code for this sub:


    Please Login or Register  to view this content.
    Thanks in advance

  2. #2
    Valued Forum Contributor AlvaroSiza's Avatar
    Join Date
    09-19-2007
    Location
    Staffordshire
    MS-Off Ver
    2007
    Posts
    591

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    Generally speaking, you should try to declare your variables as specifically as possible. In this case, shts is a Worksheet, rngs is a String, and Lastrow is a Long.

    As to No. 2, you have already assigned the Worksheet name "A2D2-Forecast" to the variable 'shts'. As your code reads, excel is looking for a worksheet literally named "Shts", which does not exist. After initially declaring shts as Worksheet and assigning via
    Please Login or Register  to view this content.
    , replace instances of
    Please Login or Register  to view this content.
    with just
    Please Login or Register  to view this content.
    Last edited by AlvaroSiza; 05-21-2012 at 11:51 AM.
    Perhaps it was the Noid who should have avoided me...
    If you are satisfied with my solution click the small star icon on the left. Thanks
    1. Make a copy of your workbook and run the following code on your copy (just in case)
    2. With excel open, press ALT+F11 to open the Visual Basic Editor (VBE). From the "Insert" menu, select "Module".
    3. Paste the code from above into the empty white space. Close the VBE.
    4. From the developer tab, choose "Macros", select the Sub Name, and click "Run".

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    bg_enigma1,

    For the first error, don't use both .Range and .Cells, just use .Cells:
    Please Login or Register  to view this content.

    For the second error, you cannot select a range unless the range is on the active (selected) worksheet, otherwise you'll get that error. So you'd need to select the worksheet first, and then select the range:
    Please Login or Register  to view this content.
    However, it is rarely necessary to select an object (or range) in order to act upon it
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    Good catch, AlvaroSiza, I didn't actually look at the full code to see that shts was a variable assigned to the worksheet name. As is, shts is just a string, to use it would be:
    Please Login or Register  to view this content.

    To set it as a worksheet instead of a string, you would use:
    Please Login or Register  to view this content.

    And then you could refer to shts directly as Alvaro pointed out

  5. #5
    Registered User
    Join Date
    05-16-2012
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    Please Login or Register  to view this content.
    Now gives me application-defined or object-defined error

    full code:
    Please Login or Register  to view this content.
    Last edited by bg_enigma1; 05-21-2012 at 12:29 PM.

  6. #6
    Registered User
    Join Date
    05-16-2012
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    is it possible that I do not have the correct DIM?
    Last edited by bg_enigma1; 05-21-2012 at 05:52 PM.

  7. #7
    Registered User
    Join Date
    05-16-2012
    Location
    Utah
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    bumping for help :\

  8. #8
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: "Wrong number of arguments or invalid property assignment" *sigh*

    bg_enigma1,

    Quote Originally Posted by bg_enigma1 View Post
    Please Login or Register  to view this content.
    Now gives me application-defined or object-defined error
    I addressed that issue in my post here:
    Quote Originally Posted by tigeravatar View Post
    As is, shts is just a string, to use it would be:
    Please Login or Register  to view this content.

    To set it as a worksheet instead of a string, you would use:
    Please Login or Register  to view this content.

    And then you could refer to shts directly as Alvaro pointed out

+ 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