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:

c01 = "{"& join(split(Windows XP|Adobe|IBM|VLC|.Net Framework|Office|Java|Windows Media|J2SE|MSXML|KKKK|PPPP|TTTT|RRRR","|"),chr(34)&"," & chr(34))& "}"

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:

Sub Parse()
     '
     ' Parse Macro
     ' Parse the files of REXEL
     '
     
    Workbooks.OpenText Filename:="C:\Users\karthic.rangaraj\Desktop\4408.csv"
     
     ' Parse it using comma and semicolon as delimiters
    Range(Range("A1"), Range("A1").End(xlDown)).TextToColumns _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=True, Space:=False, Other:=False, _
    FieldInfo:= _
    Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 2))
     
     ' Delete columns of ComputerSerial & UserName
     
     ' Columns("B:B").Select
     ' Selection.Delete Shift:=xlToLeft
     
     '  Columns("C:C").Select
     'Selection.Delete Shift:=xlToLeft
     
    Range("B:B,D:D").Delete
     'Range("D1").FormulaR1C1 = "Application_ID"
   Dim lLR As Long
Dim vArray As Variant
Dim sString As String
 '*****************************************************************************************
 'Add as many items you like to this array
 '*****************************************************************************************
                     
vArray = Array("Windows XP", "Adobe", "IBM", "VLC", ".Net Framework", "Office", "Java", "Windows Media", "J2SE", "MSXML")
For i = LBound(vArray) To UBound(vArray)
    sString = sString & Chr(34) & vArray(i) & Chr(34) & ","
Next i
 '*****************************************************************************************
 'This is the final array string that we pass to array formula
 '*****************************************************************************************
sString = "{" & Left(sString, Len(sString) - 1) & "}"
lLR = Range("A" & Rows.Count).End(xlUp).Row
Range("D2").FormulaArray = "=INDEX(" & sString & ",1,MATCH(1,--ISNUMBER(SEARCH(" & sString & ",$C2,1)),0))"

Range("D2").AutoFill Destination:=Range("D2:D" & lLR)

ActiveWorkbook.SaveAs "C:\Users\karthic.rangaraj\Desktop\4408.xls", FileFormat:=56
'56 = xlExcel8 (97-2003 format in Excel 2007, .xls)

End Sub

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.