excel vba ribbon wrong number of arguments or invalid property assignment

• Mark Forums Read
• View Site Leaders
• Knowledgebase
• Consulting Services
• PayPal Donation
• Advanced Search
• VBA Code & Other Help

Wrong number of arguments or invalid property assignment

• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Thread: Wrong number of arguments or invalid property assignment

Thread tools.

• Show Printable Version
• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode
• View Profile
• View Forum Posts

Dear Experts Following procedure was working fine. But today it has started to display this compiler error message Wrong number of arguments or invalid property assignment What is wrong? PHP Code: Private  Sub UserForm_Initialize () Dim iLastRow  As  Long         With Sheets ( "weights" )          iLastRow  = . Range ( "B"  & . Rows . Count ). End ( xlUp ). Row                If  iLastRow  >  1 Then          TextBox2 . Value  =  Format (. Cells ( iLastRow ,  "L" ),  "dd-mm-yy" )  ' this line show error                 icurRow = iLastRow         End If     End With Ens Sub 

Add this line before the offending line [vba] MsgBox . Cells ( iLastRow , "L" ).Text [/vba] and let's see.

____________________________________________ Nihil simul inventum est et perfectum Abusus non tollit usum Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen! James Thurber

Dear Sir, PHP Code: MsgBox  . Cells ( iLastRow ,  "L" ). Text  Msgbox shows=01-05-07 If I Write codes as PHP Code: TextBox2 . Value  = . Cells ( iLastRow ,  "L" )  then textbox2.value is =01/05/2007 But I want to dispaly it with following format 01-05-07 Please help

It's amazing how you shift the problem. This started as an error, and ends as a format ? [vba] TextBox2 . Value = Format(. Cells ( iLastRow , "L" ).Value, "dd-mm-yy") [/vba]

Dear Sir, I refer to another link for more understanding http://www.excelforum.com/showthread.php?t=603819 Please review

• Visit Homepage

ANOTHER cross-post? And you only give the link in post #5? This is a really good way to get yourself banned from every board on the planet tqm1. In fact I'm seriously considering banning you from this one right now...

You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you The major part of getting the right answer lies in asking the right question... Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.

And that one is different again. You're too much like hard work, I'll think I'll personally ignore your posts now.

tqm1, I have placed a temporary ban on your posting at VBAX. This ban is for repeated cross-posting and will be in effect for 48 hours. If you wish to appeal against this decision, feel free to PM me or any other administrator. Have a nice day, John

Thank you very much for sending me jail.

• Private Messages
• Subscriptions
• Who's Online
• Search Forums
• Forums Home
• Announcements
• Introductions
• How to Get Help
• Non English Help
• Access Help
• SUMPRODUCT And Other Array Functions
• Outlook Help
• PowerPoint Help
• Office 2007 Ribbon UI
• Integration/Automation of Office Applications Help
• Other Applications Help
• Testing Area
• Mac VBA Help
• Other Mac Issues
• Book Reviews

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is On
• [VIDEO] code is On
• HTML code is Off

Forum Rules

• VBA Express
• Privacy Statement

• Forgotten Your Password?

• Today's Posts
• Mark Forums Read
• Quick Links :
• What's New?
• Members List

Forum Rules

• Commercial Services
• Advanced Search

• Microsoft Office Application Help - Excel Help forum
• Excel Programming / VBA / Macros
• [SOLVED] wrong numbers of arguments or invalid property

wrong numbers of arguments or invalid property

Thread tools.

• Show Printable Version
• Subscribe to this Thread…

Rate This Thread

• Current Rating
• ‎ Excellent
• ‎ Average
• ‎ Terrible
• Linear Mode
• Switch to Hybrid Mode
• Switch to Threaded Mode

Hi, all! Got some issues with my macro. It is a very basic, simple macro, but for some reason, it stops working: Is there any chance anyone knows how to fix it? Here below is the PrintScreen from Excel, and macro itself. Range.jpg Sub Performances_Clear() Dim Answer As Integer Answer = MsgBox("Do you want to delete the data?", vbYesNo + vbCritical, "Delete Data") If Answer = vbYes Then range ("C6:C105") range("C111:C210").ClearContents range("F6:F105").ClearContents range("F111:F210").ClearContents range("C6:C105").ClearComments range("C111:C210").ClearComments range("F6:F105").ClearComments range("F111:F210").ClearComments range( _ "G6:G105,H6:H105,I6:I105,J6:J105,K6:K105,L6:L105,M6:M105,N6:N105,G111:G210,H111:H210,I111:I210,J111:J210,K111:K210,L111:L210,M111:M210,N111:N210" _ ).Select Selection.ClearContents range( _ "G6:G105,H6:H105,I6:I105,J6:J105,K6:K105,L6:L105,M6:M105,N6:N105,G111:G210,H111:H210,I111:I210,J111:J210,K111:K210,L111:L210,M111:M210,N111:N210" _ ).Select Selection.ClearComments range("C6").Select Else: MsgBox "Canceled" End If

Re: wrong numbers of arguments or invalid property

It looks like you have named something 'range' - probably a variable or function. That's not a good idea, so I suggest you change its name to something else.

Everyone who confuses correlation and causation ends up dead.

Originally Posted by rorya It looks like you have named something 'range' - probably a variable or function. That's not a good idea, so I suggest you change its name to something else. Thank you, sir! I did indeed!

Thread Information

Users browsing this thread.

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

Similar Threads

Wrong number of arguments or invalid property assignment vb6, wrong number of arguments or invalid property assignement, [solved] wrong number of arguments invalid or property assignment error, wrong number of arguments or invalid property assignment, wrong number of arguments invalid or property assignment error, wrong number of arguments or invalid property assignment, wrong number of arguments or invalid property assignment, wrong number of arguments or invalid property assignment, posting permissions.

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
• BB code is On
• Smilies are On
• [IMG] code is Off
• HTML code is Off
• Trackbacks are Off
• Pingbacks are Off
• Refbacks are Off
• ExcelForum.com

• 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

Wrong Number Of Arguments or invalid property assignment...Sometimes. Advice pls.

• Thread starter Willow123
• Start date Apr 19, 2019
• Tags activecell.offset0 code dim string v10
• Apr 19, 2019

Hello all. So I have some code that splits up a string and pastes certain parts of the string into certain columns. It works fine if you just run it from VBA editor. But it would be a nice touch if you added it to the right click mouse menu. I have some other code (not included) that works fine when I added this right click feature. This bit of code, Splitter(), ceases to function and gives the error message of " Wrong Number Of Arguments or invalid property assignment". I noticed if I commented out the right click feature that calls Splitter() the code will run properly again...I apologize for the length of code I know I can write variable declarations in a shorter form, and will do so after I get all the little parts of my program to work. Any advice would be appreciated. Code: 'Located on sheet 1 [FONT=Verdana]Sub Splitter()[/FONT] [FONT=Verdana]Dim r As Range Dim s As String Dim arr() As String Dim arr1() As String Dim arr2() As String Dim v As Variant Dim v0 As String Dim v1 As String Dim v2 As String Dim v3 As String Dim v4 As String Dim v5 As String Dim v6 As String Dim v7 As String Dim v8 As String Dim v9 As String Dim v10 As String Dim v11 As String Dim InBox As String[/FONT] [FONT=Verdana]Dim Prd As Long Dim Prd1 As Long Dim First As Long Range("B1").Select InBox = InputBox("Please Enter Sap or MOC Number") If InBox = vbNullString Then Exit Sub[/FONT] [FONT=Verdana] Do Until ActiveCell = ""[/FONT] [FONT=Verdana]s = ActiveCell.Value First = InStr(1, s, "_") v10 = Left(s, First - 1) arr = Split(s, "-") 'Code Breaks here and gives error message[/FONT] [FONT=Verdana]v1 = arr(1) v2 = arr(2) v3 = arr(3) v4 = arr(4) arr1 = Split(v4, "_") 'I assume it will break here to....[/FONT] [FONT=Verdana]v5 = arr1(0) v6 = arr1(1) arr2() = Split(v6, ".") Prd1 = InStr(1, arr2(0), "R") v7 = Mid(arr2(0), Prd1 + 1)[/FONT] [FONT=Verdana]v8 = Right(v1, 2) v9 = Left(v1, 2)[/FONT] [FONT=Verdana]ActiveCell.Offset(0, 1).Value = v10 ActiveCell.Offset(0, 4).Value = InBox & ":07 Engineering:07.15" _ & "Miscellaneous Engineering Records:07.15.02 Demolition Records" ActiveCell.Offset(0, 8).Value = "'" & v8 ActiveCell.Offset(0, 9).Value = v2 ActiveCell.Offset(0, 11).Value = v3 ActiveCell.Offset(0, 12).Value = v5 ActiveCell.Offset(0, 13).Value = v10 ActiveCell.Offset(0, 17).Value = v7 ActiveCell.Offset(0, 7).Value = v9 ActiveCell.Offset(0, 7).Value = "Issued for Demolition" Erase arr() Erase arr1() Erase arr2()[/FONT] [FONT=Verdana]ActiveCell.Offset(1, 0).Select[/FONT] [FONT=Verdana]Loop[/FONT] [FONT=Verdana]End Sub [/FONT] <strike></strike> This is part of the code that allows a user to right click to run macro. Rich (BB code): 'located in Module 1 Sub FileNames() Call Sheet1.GetNumbers End Sub Sub Split() 'If I comment this subroutine, Splitter() can be successfully executed in VBA editor Call Sheet1.Splitter End Sub  

Excel Facts

NdNoviceHlp

Well-known member.

Code: Dim arr() As Variant Dim arr1() As Variant Dim arr2() As Variant U also seem to have missed v1 = arr(0)? Anyways, HTH. Dave  

• Apr 20, 2019

Ah that did not do it. It has something to do with trying to run the macro from right clicking mouse. The string stored in arr(0) is not needed so I just leave it out. Thanks any how Dave.  

Maybe move the sub to a module and just Call Splitter. U will need to add some code to specify that the sub refer to sheet 1 ie. Code: Sheets("Sheet1").Range("B1").Select Might be worth a trial. Dave  

MrExcel MVP, Moderator

Change the name of this Sub Code: [FONT=Verdana][FONT=Verdana]Sub Split() 'If I comment this subroutine, Splitter() can be successfully executed in VBA editor[/FONT] [FONT=Verdana]Call Sheet1.Splitter[/FONT] [FONT=Verdana]End Sub[/FONT][/FONT] You should never use VBA keywords for Subs or variables.  

You can put the strin with which you have a problem. The code works for me, with the right button, with this string: "abc-def-gh-ij-k_lmnR.opq-rst"  

Similar threads

• Feb 8, 2023

• Young Grasshopper
• Dec 11, 2022
• Aug 4, 2022
• michaelsmith559
• Oct 14, 2023
• WhiteRaven76
• May 23, 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

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Excel VBA - Compile Err- Wrong number of arguments or invalid property assignment

I'm trying to code an excel button to reference a value in Column AX Row 2, enter that reference into a field in our business system(IBM), and if it finds a trim at the coordinates matching the trim from Column AX Row 2, to enter that value into the same sheet in column F row 2, then move to the next row and repeat until the row is blank.

I've tried changing to For Next and adjusting code but can't figure out where I am stuck

Compile Error: wrong number of arguments or invalid property assignment

• 1 .Column("F").row("EachRow") >> .Columns("F").Rows(EachRow) –  Tim Williams Commented Aug 19, 2019 at 22:49

one could simplify the reference from column(x).row(y) to use either with Range() or Cells() and EachRow is a variable and should be unquoted:

• It does work if you add the s and unquote the EachRow , but certainly not a common method. –  Tim Williams Commented Aug 19, 2019 at 22:52
• @TimWilliams really? Wow did not think it would. –  Scott Craner Commented Aug 19, 2019 at 22:53
• Well a Column is a Range and a Range has rows so... I did have to check though. –  Tim Williams Commented Aug 19, 2019 at 22:54
• I can see the logic, and "learnt me" my new thing today. cool. –  Scott Craner Commented Aug 19, 2019 at 22:56
• 1 Congrats on the 100.000 :) –  Vityata Commented Aug 19, 2019 at 23:01

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Sign up or log in

Post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .

Not the answer you're looking for? Browse other questions tagged excel vba or ask your own question .

• The Overflow Blog
• Scaling systems to manage all the metadata ABOUT the data
• Navigating cities of code with Norris Numbers
• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Bringing clarity to status tag usage on meta sites
• Tag hover experiment wrap-up and next steps

Hot Network Questions

• If there is no free will, doesn't that provide a framework for an ethical model?
• can a CPU once removed retain information that poses a security concern?
• Word to classify what powers a god is associated with?
• Rock paper scissor game in Javascript
• What does the \end mean in LaTeX's environment?
• Prove that there's a consecutive sequence of days during which I took exactly 11 pills
• The minimal Anti-Sudoku
• When is internal use internal (considering licenses and their obligations)?
• Conditional environment using package option
• On object cannot be moved along X
• Can you bring a cohort back to life?
• How to Handle a Discovery after a Masters Completes
• Do "Whenever X becomes the target of a spell" abilities get triggered by counterspell?
• DIN Rail Logic Gate
• A burning devil shape rises into the sky like a sun
• Why HIMEM was implemented as a DOS driver and not a TSR
• Is there any point "clean-installing" on a brand-new MacBook?
• Should I pay off my mortgage if the cash is available?
• Short story about a committee planning to eliminate 1 in 10 people
• Output of a Diffractometer
• Has the application of a law ever being appealed anywhere due to the lawmakers not knowing what they were voting/ruling?
• "Heads cut off" or "souls cut off" in Rev 20:4?
• What was Bertrand Russell's take on the resolution of Russell's paradox?
• Is it illegal to allow (verbally) someone to do action that may kill them?