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

[karthikin]

This is my formula to find the lookup array values from col C and paste it in the Col D.

Please Login or Register  to view this content.

If i enter more than 10 items in here, lets say 12 items I getting a error message "Unable to set the property of the class range FormulaArray in VBA"

Please Login or Register  to view this content.

I want to enter 30 items in the array & perform the match the values & paste in the COl D!!

Thank u guys!!

[snb]

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.

[mohd9876]

try using .Formula instead of .FormulaArray

Please Login or Register  to view this content.

[romperstomper]

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.

[karthikin]

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

[karthikin]

@snb : could you please explain me a bit more. How can i integrate your formula in my code. Thank you.

[snb]

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.

[karthikin]

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.

[Andy Pope]

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.