Cells VBA Function

[JJJ55]

I have just got a new version of excel and the cells property in VBA no longer works and I am shown the following error message. "Wrong number of arguments or invalid property assignment".

This happens even when writing the simplest code.

Are there settings that need to be changed?

 

[diddi]

post your code.
I use 2010 and Cells( ) works fine. it would not have been altered as backward compatibility would be lost.

 

[JJJ55]

Here is the simple code I am trying to run

For i = 1 To 100
Cells(i, 1).Value = i ^ 2
Next

 

[diddi]

it is better programming to tell vba which sheet you are writing to, as vba runs on the active sheet, so if for some reason you are not on the right sheet, you will get strange results. assuming you are on sheet1:

    For i = 1 To 100
        Sheets("Sheet1").Cells(i, 1).Value = i ^ 2
    Next I     <-----------note the I on the end

PS if you are new to coding, can I encourage you to use meaningful variable names. it will help when your code is lengthy and you are revisiting it in 12 months time

 

[My Aswer Is This]

Your Quote: "I have just got a new version of excel "
What version of Excel did you just get?

 

[JJJ55]

Thanks. So do you always have to declare sheet when using the cells property? When previously doing so, I don't remember having to do so.

 

[My Aswer Is This]

Your simple script ran OK on my computer using Excel 2013. That's why I asked what new version you are using.

Thanks. So do you always have to declare sheet when using the cells property? When previously doing so, I don't remember having to do so.

 

[diddi]

if you never change the sheet you are looking at, then you wont notice anything. but it is good programming practise and I always do it.
if you are doing a lot with cells in a sub you like do this...

    With Sheets("MySheet")
        .cells(1,5)= 5 * .cells(3,7)
        .range("A5") = "hello"
    End With

 

[JJJ55]

It is also 2013. Previously had 07 and never had issues with running the Cells property, without declaring the sheet.

 

[My Aswer Is This]

No I never declare sheet names if it's for the active sheet and I don't normally use the Next i unless I'm using several next statements just to keep me from getting confused.

It is also 2013. Previously had 07 and never had issues with running the Cells property, without declaring the sheet.