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

[Willow123]

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.

'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.

'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

 

[NdNoviceHlp]

Dim arr() As Variant
Dim arr1() As Variant
Dim arr2() As Variant

U also seem to have missed v1 = arr(0)?
Anyways, HTH. Dave

 

[Willow123]

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.

 

[NdNoviceHlp]

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.

Sheets("Sheet1").Range("B1").Select

Might be worth a trial. Dave

 

[Fluff]

Change the name of this Sub

[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.

 

[DanteAmor]

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"