score:4

Accepted answer

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.

score:2

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"

Related Query

More Query from same tag