Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Run-Time error '5': Invalid procedure call or argument

0

Hello Experts,

I am attaching herewith the excel sheet with codes, It appears to show debugging with an error message (Run-Time error '5': Invalid procedure call or arguments) once its process.

Please provide your support to resolve this issue. 

Thanks

excel
MS Office Version: office 365
posted by: ANSCOM (rep: 2) on Mon Jan 9, 2023 at 7:41 am
Answer
Discuss

Discussion

Hello ANSCOM and welcome.

I downloaded your file, reviewed your macro code, then clicked the "TRY" button. Everything ran fine - no Run-Time error. (Excel 2007). Since I didn't get the error I don't attempt to "fix what isn't broken".
WillieD24 (rep: 557) Jan 9, '23 at 2:06 pm
Hi ANSCOM

I got similar to Willie (no error) but, given your several On Error lines, gave an answer for another error the code incurred.
John_Ru (rep: 6142) Jan 9, '23 at 3:01 pm
Add to Discussion

Answers

0
Selected Answer

Hi ANSCOM

Like Willie (but with Excel365), I ran the code from the button and didn't get that error 5 (and the code created a new sheets "aa1" and "dd1")

(I note that Sheet1 has duplicate supplier values aa1 in B6 and B11- if you change B11 to ab1 say, you'll get summary sheet for ab1 too.) 

That said, that's because your macro test1 contains several lines for On Error.... (not sure why you need the duplicates!) which would mask any error. If I comment all these lines out, I get run error '457' on line:

z.Add Array(a(i, 2), CreateObject("System.Collections.Arraylist")), CStr(a(i, 2))

related to a duplicate key- like the second aa1 (which might be avoided by checking first with IsObject before doing an Add to the Collection z)).

Likewise, there's also an Error 9 related to this line:

Sheets(z(i)(0)).Delete

where I guess you're trying to delete a sheet number which does not exist- please check.

If this sorts your problem, please remember to mark ths Answer as Selected. If not and you still get Error 5, please tell us on which line it occurs (using the Debug button). Step through the code if necessary.

posted by: John_Ru (rep: 6142) on Mon Jan 9, 2023 at 3:01 pm
Discuss

Discussion

Firstly, thank you for your support and I still face the same issues.

The purpose of this is to copy by group data and paste as a separate sheet per supplier name with criteria Column # 10>0 and Column A = "Grand Total".
I also need to find duplicate suppliers.   

Getting an error on the line below "If Not z(CStr(a(i, 2)))(1).contains(CLng(1)) Then"  

Should I make any adjustments to my computer if it works with you without any issues because of MS 365 ?

Please advise..   Thanks
ANSCOM (rep: 2) Jan 10, '23 at 1:14 am
I'll try to look st this again later but did you create this code (if so, where is the test/ actions for duplicate supplirrs?)

You introduced a new error- what did you intend that gest to achieve?

When / where does the Error 5 occur?
John_Ru (rep: 6142) Jan 10, '23 at 2:52 am
Thank you for your support, Mr. John.
I have a friend who has created this code for me, and he says that it works fine without any errors.". as you informed.
also I have tried to unistall and re-install the office 365, But no result. 

Sorry to say that, I am not familiar with the coding. In order to resolve this issue, I am seeking the assistance of an expert. 

Please note that I have already added the screen short of the error in the first post. Note that I have already included a screen shot of the error in the first Post.  
Thanks
ANSCOM (rep: 2) Jan 10, '23 at 3:08 am
Haven't had chance to look yet but yiu can't upload anything but an Excel file on this Forum (do we can't see your screenshot) You could embed it in your Excel file in the question (try to get VBProject Explorer showing the failed line too).

Secondly it's near impossible to diagnose any PC problems remotely (and that's not the purpose of this Forum) but why aren't you asking your friend locally? They seem to know some  VBA. 
John_Ru (rep: 6142) Jan 10, '23 at 6:21 am
As WillieD24 mentioned, I would uninstall MS 365 and install MS Excel 2007. As my friend has the same version, I am hoping that it will work without any issues. That way I can make sure this code doesn't work with MS 365.

It was not my intention to examine my computer. I only asked if any changes needed to be made to VBA options such as the VBA Option, Referances, and Add-Ins.etc.. I was recommended this forum by my friend (the one who wrote the VBA code). You will be able to receive assistance from many experts in this Forum as he said.

However, I would like to thank you for your tremendous support. Have a nice day..
ANSCOM (rep: 2) Jan 10, '23 at 8:35 am
Thanks for selecting my answer, Anscom but I don't think I solved your problem.

Regarding your friends comment on "many experts", in reality it's generally very few people who provide the help to Forum users. 
John_Ru (rep: 6142) Jan 10, '23 at 8:43 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login