about error 450: Wrong number of arguments or invalid property assignment

Hi,
What I did was simply to change date 'MMM-YY' based on user inputs. The code goes like this:

Sub ChngMonth()
MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
If MMMYY = "" Then Exit Sub
ddmmmyy = "15-" & MMMYY
For i = 0 To 330 Step 30
j = 12 - i / 30
Worksheets("Monthlist").Cells(1, j) = "'" & format(CDate(ddmmmyy) - i, "mmm-yy")
Next
End Sub

When I run it, I will have 450 for 'format()' statement. But the funny thing is I don't always get that error. For some workbooks, I do; the others, I don't. I guess it has something to do with the way the book was set up; or some options that I didn't select. If the problem is with "Wrong number of arguments", how come for some workbooks it works, some not, using the same statement.
Thanks in advance.
Feipe

Excel VBA question moved to Office Development

Put "Option Explicit" at the very top of your module.

Change the names of your variables ... MMMYY seems dangerous to me ... and properly define them ... something like:

Dim str_MMMYY As String
Dim str_ddmmmyy As String

That way there is no question of a Namespace overlap.

I'll look at the rest of your code later.

Art,
I changed the code the way you recommended but I still got '450'.
Thanks again.
Feipe

option explicit

Sub ChngMonth()
Dim str_MMMYY, str_ddmmmyy As String
Dim i, j As Integer

str_MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
If str_MMMYY = "" Then Exit Sub
str_ddmmmyy = "15-" & str_MMMYY
For i = 0 To 330 Step 30
j = 12 - i / 30
Worksheets("Monthlist").Cells(1, j) = "'" & format(CDate(str_ddmmmyy) - i, "mmm-yy")
Next
End Sub

One minor problem (but it doesn't generate any errors):

Code:

---

    Dim str_MMMYY, str_ddmmmyy As String  '< Leaves str_MMMYY as VARIANT!
    Dim i, j As Integer                  '< Leaves i as VARIANT!

---

I plugged in this code into a new clean workbook with a default blank sheet "Sheet1", and it correctly puts 12 monthly headers across the top row of the sheet from right to left starting with Mar-06 at the right and ending with Apr-05 at the left. It runs fine in both Excel '97 and Excel 2003:

Code:

---

Option Explicit

Sub ChngMonth()
    Dim str_MMMYY As String, str_ddmmmyy As String
    Dim i As Integer, j As Integer

    str_MMMYY = InputBox("Enter The Last Data Month (mmm-yy):")
   
    'TEST TEST TEST TEST
    str_MMMYY = "March 2006"
    'END TEST
   
    If str_MMMYY = "" Then Exit Sub
    str_ddmmmyy = "15-" & str_MMMYY
   
    Debug.Print str_ddmmmyy
   
    For i = 0 To 330 Step 30
        j = 12 - (i / 30)
        MsgBox Format(CDate(str_ddmmmyy) - i, "mmm-yy")
        Worksheets("Sheet1").Cells(1, j) = "'" & Format(CDate(str_ddmmmyy) - i, "mmm-yy")
    Next
End Sub

---

Art,
Thanks for the attempt! Please read my first email: "...funny thing is...". I have no problem with a fresh book, even using my code. The problem is with the existing books, or some of them. Could you try your code or mine in your existing books and see what happens?
Highly appreciate it.
feipe.

Let's find out exactly what is broken ...

Right after the "j = 12 - (i / 30)" line, add the following to YOUR code:

Code:

---

'Test access to the desired sheet name
MsbBox ActiveWorkbook.Sheets("Monthlist").Name
MsbBox ActiveWorkbook.Worksheets("Monthlist").Name
'Test access to the desired Format function
MsgBox Format(CDate(str_ddmmmyy) - i, "mmm-yy")

---

Which line reports the error?

Try explicitly forcing loading the VALUE property:

Code:

---

Worksheets("Sheet1").Cells(1, j).Value = "'" & Format(CDate(str_ddmmmyy) - i, "mmm-yy")

---

I tried almost all of them before I came to the forum. None of them worked since it's an error during compilation. It won't even take 'On Error Goto Resume', let alone anything else.
Thanks again, please don't spend too much time on it. It's not worth it. I'll try to figure out a way of avoiding VBA Format().
Thanks anyway.
Feipe

Hi buddy,

I got a solution to this problem. I got a similar problem in one of the worksheet in the workbook. So the code

year(now) was working in some userforms but not in other userform. So i checked whether any of the object's name on the userform is identical with the function name "year".

infact I have defined name and caption property of a label as 'year', hence when i was using year function in code for userform_activate() I was getting the error "450, wrong type of arguments", but after changing the name and caption property of that label from 'year' to 'yy', this code started working.

Hence I suggest you to check in your worksheet or userform, whether you have defined any property having the name identical to the function for which you are getting this error. If yes then change that property to something else other than the function's name and the function should work for you...:)

Thanks nd regards
Shekhar
Love all serve all!!!