Solved: AddCustomList Trouble

[compariniaa]

I have the following code to add a custom list:
Sub VBAX_Example()
Dim r As Long
Dim i As Long
Dim FileArray(240) As String
i = 0
For r = 2 To 241
i = i + 1
FileArray(i) = sheets("Sheet1").Cells(r, "O")
Next r
Application.AddCustomList ListArray:=FileArray
End Sub
However, when I try to run it, I get "Run-time error '1004': Method 'AddCustomList' of object '_Application' failed"
I have no idea why I'm getting that wrong. My code seems to be just like threads I found on this site and similar to Excel Help's example. Can anybody help me?

[mdmackillop]

Works for me, but I'm getting an empty entry for the first item. Move your increment as follows to resolve this.

For r = 2 To 241
FileArray(i) = Sheets("Sheet1").Cells(r, "O")
i = i + 1
Next r

[compariniaa]

I opened a new book and tried the code. At first I just made 28 random entries to test it and it worked. So I tried the full list (all 240 entries) and got the error again.

It seemed like my list was just too big, so I limited it to 25 and it worked. Apparently the addcustomlist has either a character limit or an entry limit. If it's an entry limit, then the number is 120 (which I discovered after a bit of testing).

And I see what you mean about the blank first entry MD, and I think it's because I was coding using "Option Base 1". You must not have had that, so it made entry 0 for the array a blank, then started with entry 1.

thanks for all the help MD