CODEWITHSUNDEEP
vbaexcel
Ans
Ans

Reputation: 1234

Excel vba formula string: array formula - how to simulate Ctrl+Shift+Enter press

I have a worksheet with data and some columns which I fill with formulas via vba. The formula I struggle with is an array formula that looks like this:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
            "{=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
              "MAX(IF(sheet1!B2:M2>$FY" & currentRow & "," & _
                                "sheet1!$B$1:$M$1)))+1}"

It's supposed to be an array formula, so that's why I put {} there. However, when run it simply display the formula's text in a cell, without calculating it. I have to manually remove the brackets, and then press Ctrl+Shift+Enter myself.

Is there any way to avoid it? I have a great many rows and I can't ctrlshiftenter each.

I tried running it without brackets, it works, but gives a #VALUE! error, which can also be fixed by applying Ctrl+Shift+Enter.

Upvotes: 3

Views: 2423

Answers (2)

user4039065
user4039065

Reputation:

To create an array formula with a simulated ctrl+shift+enter (aka CSE), use the Range.FormulaArray Property instead of the Range.Formula Property and let Excel add the 'curly braces'.

with Workbooks(job_file).Worksheets(1)
    .Cells(h + b, 195).FormulaArray = _
        "=IF(MAX(IF(B2:M2>$FY" & currentRow & ",$B$1:$M$1))=0,0," & _
            "MAX(IF(sheet1!B2:M2>$FY" & currentRow & ", sheet1!$B$1:$M$1)))+1"
end with

I noticed in your formula that you use B2:M2 and sheet1!B2:M2. Shouldn't they both be sheet1!B2:M2?

There are some considerations.

  • Runtime Error: 1004 - Too long. There is a reduced character limit of 255 for FormulaArray but there are work-arounds.

  • Runtime Error: 1004 - Broken String. Remember that all quotes within a quoted string must be doubled up. This is easily one of the most common causes of errors when trying to write a formula into a cell through VBA. Hint: TEXT(,) can be used instead of "" so you don't have to type """" for a zero-length string.

  • FormulaArray accepts both xlR1C1 and xlA1 style formulas. If you can wrap your head around xlR1C1 style formula syntax, it is generally easier to construct a concatenated formula string in xlR1C1 since you can use digits to represent column numbers instead of trying to convert column ordinals to a column letter. However, do not try to mix-and-match xlA1 and xlR1C1 range references in the same formula; they must all be one style or the other.

  • If you are having trouble formulating a string that will be accepted as a formula, put a tick (e.g. ' ) in front of the first equals sign then run the code and return to the worksheet to see what was put in. Make modifications on the worksheet until you have a working formula then transfer those modifications to the VBA code.

Upvotes: 4

tigeravatar
tigeravatar

Reputation: 26660

As a note, it looks like this can be done without an array formula, like so:

Workbooks(job_file).Worksheets(1).Cells(h + b, 195).Formula = _
        "=IF($FY" & currentRow & ">MAX(B2:M2),0," & _
         "MAX(INDEX((B2:M2>$FY" & currentRow & ")*$B$1:$M$1,)))+1"

Upvotes: 2

Related Questions