Hello!

I'm having trouble inserting an array formula with VBA as it returns the 1004 error ("Unable to set the FormulaArray property of the Range class"). I've found out this is due to the string being too long (max 255 chars). Searching the net and forums, the general suggestion is to use a replace function to split it up and in general clear up the formula. However, when I do this myself, I still experience the same error message, even though I've broken my formula up into three distinct parts, each less than 255 chars.

The idea is that we have a spreadsheet containing the basic data about a number of research proposals, one proposal per row. The spreadsheet consists of a number of data sheets, e.g. one containing the proposal details themselves, one containing assessment details, one containing details about the people and institutions involved in each proposal, etc. These are all combined in different ways in one central viewing sheet which is the only part seen by the end user. For each funding scheme, we might have a number of assessment criteria. So in this particular scheme, we have two peer reviewers assessing each application and each assigning three different scores. The scores themselves are stored in the data sheet containing assessments. Now, these sheets are often very large and slow when they pull them from Sharepoint, so instead of working with an active sheet which is always updated with the newest data, they copy and paste everything as values as the first thing they do when they get the sheet. Then they work from that.

However, as the assessments come after the proposals themselves have been received, we need to be able to update the data source (easy enough) and re-insert the active formulas so that the scorings are fetched for each proposal - and also make sure it's the newest assessment in case of resubmission of the assessment. Now, my colleagues could conceivably have a list of formulas to use and just paste them in as needed. However, I just want them to have a button which inserts the formula with them having to do nothing else.

Imagine therefore two tables with the following columns (descriptions in parentheses)

Table 1: Viewing table: Sagsnr (case no.), applicant, title, etc, Scientific Quality
Table 2: Data table (called "Bedømmelser"): Sagsnr (case no.), Type (assessor role), Parameter (kode) (which type of assessment to fetch), Indsendelsestidspunkt (time of submission)


Below is a minimal (non) working example containing the code:

Sub InsertGradingFormula()

Dim strBedømmelser As String
Dim strRolle As String
Dim strParameter As String

strBedømmelser = "Bedømmelser"
strRolle = "1. forbehandler"
strParameter = "scientificquality_score"

Range("B2").FormulaArray = "=IFERROR(VALUE(INDEX(" & strBedømmelser & "[#Data],(MATCH(MAX(IF(" & strBedømmelser & "[Sagsnr]=$A8,IF(" & strBedømmelser & "[Type]=" & Chr(34) & strRolle & Chr(34) & ",IF(" & strBedømmelser & "[Parameter (kode)]=" & Chr(34) & strParameter & Chr(34) & "," & strBedømmelser & "[Indsendelsestidspunkt]),),),)&$A8&" & Chr(34) & strRolle & Chr(34) & "&" & Chr(34) & strParameter & Chr(34) & "," & strBedømmelser & "[Indsendelsestidspunkt]&" & strBedømmelser & "[Sagsnr]&" & strBedømmelser & "[Type]&" & strBedømmelser & "[Parameter (kode)],0)),9))," & Chr(34) & "" & Chr(34) & ")"

End Sub
As you will see, in order to have a formula/function that is somewhat versatile, I've replaced some of the values with variables.

How can I split this up in several parts that it might work?

NOW, here's the tricky bit. If I had organisational control, I could have made another non-array formula. However, this entire solution is delivered by another bureau in the agency, and they won't support us and our spreadsheets unless we use the formulas provided by them. Therefore it is not a solution to use either helper columns or make another formula.

How would you do it?

Best regards,
Kasper