Unable to set the property of the class range FormulaArray in VBA

I don't get an error.
What do you assign to this array ?

you can also try:

Please Login or Register  to view this content.

that saves you a loop.

You are limited to about 255 characters in the FormulaArray property. If you put your search array into a range, it would make the formula simpler, and the code easier to maintain.

Thanks guys, since I'm limited to enter only 255 characters so I couldn't enter more than lets say 10 items in my array

vArray = Array("Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML")

How can split my array into multiple arrays because I would like to have more than 30 items.

ThisWorkbook.Names.Add Name:="MyList", RefersTo:=Array(Array("Windows XP", "Adobe", "IBM"))

& refer to my array formula here:

Range("D2").FormulaArray = "=INDEX(" & sString & ",1,MATCH(1,--ISNUMBER(SEARCH(" & sString & ",$C2,1)),0))"

@mohd9876: your formula didn't work, it simple put the name space elements in the all the cels of col d.

I shared the original file here: http://www.uploadmb.com/dw.php?id=1324468049

I think you'd better explain what you want, because if i get it right you are designing a circular formula.
Please post a sample workbook.

ok. I have a CSV file to remove the special characters http://www.uploadmb.com/dw.php?id=1324468049 then after removing 2 cols finally I have col A, B, C.

In col C, i have bunch of software names like this for example "Adobe Download Manager 2.0 (Supprimer uniquement)". So in col C i want to look for these values

"Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML"

if match found put in the Col D.

According to my formula I couldn't enter more than 255 ch in the array.(I have more than 50 items to find out from col Cit could be around 600 characters).

I simply don't know how to solve this. Thank you very much.

Here is my full code:

Please Login or Register  to view this content.

This this blog entry on the limitation and how to get around it by using a second step to replace short list with long list.
http://www.dailydoseofexcel.com/arch...rmulas-in-vba/

You may still run into formula length issues depending on the items in the list.