FormulaArray problem

[voiD]

Hello,

I want to assign formula to a cell's FormulaArray property via VBA code.The formula is correct, but when I try to make the assignment, I receive the error message, "Unable to set FormulaArray property of the range class". The formula is stored in a string variable. When execution of the code is stopped due to the error and I switch to debug mode and modify something (e.g. hit a space at the end of the string) in it the string content in the watch window, the execution can be continued without any problem. My question is, what happens when I change the content of the string in the watch window? The change what I make, doesn't change the formula in the string.
Any piece of help or tip would be highly appreciated!

Thanks in advance!
VoiD

 

[Rx_]

An array formula can be identified by the braces ({}) surrounding the formula. To create an array formula in Visual Basic for Applications, you can use the
FormulaArray property. However, you cannot create a formula that has more than 255 characters by using this property. Note that the Formula property in Visual Basic for Applications does not have this limit. Hope that helps. As you noted, these can be a little difficult to debug.

Dim ObjXL As Excel.Application
Set ObjXL = New Excel.Application
ObjXL.EnableEvents = False
ObjXL.DisplayAlerts = False

ObjXL.Range("C2").Select
ObjXL.Selection.NumberFormat = "0"
ObjXL.Selection.FormulaArray = _
"= AVERAGE(IF((SUBTOTAL(3,OFFSET(R[4]C[20]:R[" & intMaxRecordCount & "]C[20], ROW(R[4]C[20]:R[" & intMaxRecordCount & "]C[20])-MIN(ROW(R[4]C[20]:R[" & intMaxRecordCount & "]C[20])),0,1)))*(R[4]C[15]:R[" & intMaxRecordCount & "]C[15]= ""Original""),R[4]C[20]:R[" & intMaxRecordCount & "]C[20]))"
' Example of an Array Fromula

 

[voiD]

Thx for the reply!

I've already read a lot of about problems with FormulaArray in VBA, and tried all the mentioned solutions and workarounds, but non of them was solution to my problem. My formula size doesn't exceed the 255 char limitation and as I've mentioned it is syntactically correct. The formula looks like this:

=MATCH("ConditionValue1"&"ConditionValue2"&"ConditionValue3",Range1&Range2&Range3,0)

Condition values are strings, quotation marks are also ok.
Unfortunately, I run out of ideas. :-(

 

[voiD]

FormulaArray problem - alternative solution

Hello again,

Although, I couldn't point out what is the problem, I found an other solution, which provides the same result and the problem, I mentioned, doesn't appear with it. Below is an example with both way.

Public Sub test()
Dim rngAll As Range, rngRes1 As Range, rngRes2 As Range
Dim sF1 As String, sF2 As String

    Set rngAll = Worksheets("Sheet1").Range("B2:D6")
    
    sF1 = "=MATCH(1,(" & Range(rngAll.Cells(1, 1), rngAll.Cells(rngAll.Rows.Count, 1)).Address & _
            "=''d'')*(" & Range(rngAll.Cells(1, 2), rngAll.Cells(rngAll.Rows.Count, 2)).Address & "=''g'')*(" & _
            Range(rngAll.Cells(1, 3), rngAll.Cells(rngAll.Rows.Count, 3)).Address & "=''n''),0)"

    sF2 = "=MATCH(''''d''''&''''g''''&''''n''''," & Range(rngAll.Cells(1, 1), rngAll.Cells(rngAll.Rows.Count, 1)).Address & _
            "&" & Range(rngAll.Cells(1, 2), rngAll.Cells(rngAll.Rows.Count, 2)).Address & "&" & _
            Range(rngAll.Cells(1, 3), rngAll.Cells(rngAll.Rows.Count, 3)).Address & ",0)"

    Set rngRes1 = Worksheets("Sheet1").Range("M1")
    Set rngRes2 = Worksheets("Sheet1").Range("M2")
    
    sF1 = Replace(sF1, "''", """")
    sF2 = Replace(sF2, "''", """")
    
    rngRes1.FormulaArray = sF1
    rngRes2.FormulaArray = sF2
    
End Sub

I just shared, maybe somebody knows if the second version why doesn't work and of course if somebody meet with the same problem, can be useful in order not to spend so much time with it like I did.

Regards,
VoiD