This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Wrong number of arguments or property assignment not valid
- 8 contributors
An assignment has been attempted that is not valid.
To correct this error
Check that the number of arguments you have supplied matches the number required by the target.
Check the property assignment.
- Assignment Operators
Additional resources
- Search forums
- Board Rules
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
- If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
- Question Forums
- Excel Questions
Debug wrong number of arguments or invalid property assignment error.
- Thread starter Thread starter Harshil Mehta
- Start date Start date Oct 6, 2020
- Tags Tags #excel #vba
Harshil Mehta
Board regular.
- Oct 6, 2020
I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password. The below code gives an error msg "wrong number of arguments or invalid property assignment" highlighting the word FORMAT. This code used to work perfectly before adding the the 7th sheet. The 7th sheet is a place where Raw Data is dumped and with the help of the VBA Code the data is populated into different sheets. Could anyone please help me? VBA Code: Sub Make_SaveAs() Dim ArrayOne() As String Dim Mypassword As String Application.ScreenUpdating = False Mypassword = Sheets(6).Range("E2") ReDim ArrayOne(1 To 5) For i = 1 To 5 ArrayOne(i) = Sheets(i).Name Next Worksheets(ArrayOne()).Copy With ActiveWorkbook .SaveAs Filename:=Environ("USERPROFILE") & "\Desktop\" & Format(Now(), "YYYYMMDD -") & " Client Design - " & ThisWorkbook.Sheets(6).Range("D2") & "_" & ThisWorkbook.Sheets(6).Range("C2") & ".xlsb", FileFormat:=xlExcel12, Password:=Mypassword .Close SaveChanges:=False End With Application.ScreenUpdating = True MsgBox "File Saved on Deskstop" End Sub
Excel Facts
Well-known Member
Is it possible your desktop is connect to OneDrive? Look at the differences between these two msgboxes. VBA Code: MsgBox Environ("USERPROFILE") & "\Desktop\" MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\"
davesexcel said: Is it possible your desktop is connect to OneDrive? Look at the differences between these two msgboxes. VBA Code: MsgBox Environ("USERPROFILE") & "\Desktop\" MsgBox CreateObject("WScript.Shell").specialfolders("Desktop") & "\" Click to expand...
So was there a difference in the msgboxes? Step through the code and see if the string variables do not contain illegal characters. VBA Code: Sub Make_SaveAs() Dim ArrayOne() As String Dim Mypassword As String Dim dr As String, dt As String, nm As String, fBM As String Dim sh As Worksheet Set WS = Sheet6 dr = CreateObject("WScript.Shell").specialfolders("Desktop") & "\" 'dr = "C:\Users\davem\OneDrive\Desktop\" dt = "Client Design - " & Format(Now(), "YYYYMMDD -") nm = WS.Range("D2").Value & "_" & WS.Range("C2").Value & ".xlsb" FNM = dr & dt & nm Application.ScreenUpdating = False Mypassword = WS.Range("E2") ReDim ArrayOne(1 To 5) For i = 1 To 5 ArrayOne(i) = Sheets(i).Name Next Worksheets(ArrayOne()).Copy With ActiveWorkbook .SaveAs Filename:=dr & dt & nm, FileFormat:=xlExcel12 ', Password:=Mypassword .Close SaveChanges:=False End With Application.ScreenUpdating = True MsgBox "File Saved on Deskstop" End Sub
davesexcel said: So was there a difference in the msgboxes? Click to expand...
Okay. I was not getting a format error.
davesexcel said: Okay. I was not getting a format error. Click to expand...
MrExcel MVP, Moderator
It sounds like you created a routine/variable called Format.
Make sure that you do not have subs, functions or variables called Format
Similar threads
- Question Question
- Oct 2, 2024
- Oct 10, 2024
- Nov 22, 2022
- OilEconomist
- May 5, 2024
- Feb 9, 2024
Forum statistics
Share this page.
We've detected that you are using an adblocker.
Which adblocker are you using.
Disable AdBlock
Disable AdBlock Plus
Disable uBlock Origin
Disable uBlock
Excel Top Contributors: HansV MVP - Andreas Killer - Ashish Mathur - Jim_ Gordon - Jeovany CV ✅
November 12, 2024
Excel Top Contributors:
HansV MVP - Andreas Killer - Ashish Mathur - Jim_ Gordon - Jeovany CV ✅
- Search the community and support articles
- Microsoft 365 and Office
- Search Community member
Ask a new question
Wrong number of arguments or invalid property assignment error
With ws .Select tlRow = .Cells(Rows.Count, "A").End(xlUp).Row Rows("42:42").Select Selection.RowHeight = 35 '------------------------------------------------------------------------------------- .Range(rwHgt12_47 & ":" & rwHgt12_47, rwHgt12_48 & ":" & rwHgt12_48, rwHgt12_50 & ":" & rwHgt12_50, rwHgt12_51 & ":" & rwHgt12_51, rwHgt12_55 & ":" & rwHgt12_55, rwHgt12_56 & ":" & rwHgt12_56).Select .Range("A" & rwHgt12_56).Activate Selection.RowHeight = 12
I am getting the above error message on the highlighted line and I can;'t figure out what the issue is or how to fix it.
Report abuse
Reported content has been submitted
A Range object may take either one or two parameters, You need to use Application.Union to create a range with multiple areas.
Dim RR As Range Set RR = Application.Union( _ Range(rwHgt12_47 & ":" & rwHgt12_47), _ Range(rwHgt12_48 & ":" & rwHgt12_48), _ Range(rwHgt12_50 & ":" & rwHgt12_50), _ Range(rwHgt12_51 & ":" & rwHgt12_51), _ Range(rwHgt12_55 & ":" & rwHgt12_55), _ Range(rwHgt12_56 & ":" & rwHgt12_56)) RR.Select
1 person found this reply helpful
Was this reply helpful? Yes No
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
Thanks for your feedback.
Replies (2)
Question info.
- Other/unknown
- Office 2010
- Norsk Bokmål
- Ελληνικά
- Русский
- עברית
- العربية
- ไทย
- 한국어
- 中文(简体)
- 中文(繁體)
- 日本語
IMAGES
VIDEO
COMMENTS
If you wish to remove it you can do so in two places: (1) Do While ls = True can be truncated to Do While ls and (2) If IsEmpty(Cells(i, j).Value2) = True Then can be reduced to If IsEmpty(Cells(i, j).Value2) Then.
The number of arguments to a procedure must match the number of parameters in the procedure's definition. This error has the following causes and solutions: The number of arguments in the call to the procedure wasn't the same as the number of required arguments expected by the procedure.
assuming word_fichier was a Document variable. You also need to change 'wdFindContinue' to a literal value if you haven't added a reference to the Word object model.
I am trying to add a button that runs the macro ContinuousPageNumbers1 from a Ribbon button. The button shows up fine. The procedure is in the same template as the ribbon modification. The onAction is "ContinuousPageNumbers1." When I click on the button I get the following error: If I go into the vba editor to the macro, I can run it fine.
I am receiving a 'Run Time Error 450 - Wrong number of arguments or invalid property assignment" in an Excel 07 macro. I am working on an Excel macro that pulls in a txt file into Excel. The text is supposed to be written to one column, but on occasion a tab will be inserted which will cause a second column ("B") to be created.
I am getting the above error when I run my For Loop and this error only occurs when the ctrl is Frame1.
An assignment has been attempted that is not valid. To correct this error Check that the number of arguments you have supplied matches the number required by the target.
Debug wrong number of arguments or invalid property assignment error. I have 7 sheets in total and want to make save as for the first 5 sheets with time stamp and password. The below code gives an error msg "wrong number of arguments or invalid property assignment" highlighting the word FORMAT.
The main problem is that you named the macro Columns, which is also a keyword in Excel VBA. There is also a lot of superfluous code resulting from the recording process. Here is a renamed and somewhat streamlined version: ' FormatSheet Macro. Range("E:E").Cut. Range("A:A").Insert Shift:=xlToRight. Range("E:E").Cut.
I am getting the above error message on the highlighted line and I can;'t figure out what the issue is or how to fix it.