excel unable to set the formulaarray property of the range class

[kelvin_9]

how can i break the below array formula to two when loading macro to avoid the limitation of 255 characters?
thanks guys.

Selection.FormulaArray = _
"=IF(INDEX(schedule!C[1],MATCH(result!RC1,schedule!C1,0))="""","""",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(schedule!C[1],MATCH(result!RC1,schedule!C1,0)):INDEX(schedule!C[1],MATCH(result!R[1]C1,schedule!C1,0)-1),5)),1)),""hh:mm"")&"" - ""&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(schedule!C[1],MATCH(result!RC1,schedule!C1,0)):INDEX(schedule!C[1],MATCH(result!R[1]C1,schedul" & _
"1),7,5)),0)),""hh:mm""))"

 

[kelvin_9]

any help?
thanks

 

[DanteAmor]

Maybe we can do the formula again. But you would have to explain with data what you have and what you expect of result.


For now I see a couple of things that do not make sense to me.

Colon: It is not a parameter separator or if you are concatenated the colon, should be in quotes

MATCH(result!RC1,schedule!C1,0)):INDEX(schedule!C[1]

At the end of the formula it says:

"R [1] C1, schedul" & "1"

should that say "schedule"

 

[kelvin_9]

thank you DanteAmor for pointing me the wrong copy

my file was uploaded here and i have some questions would like to figure out.
also i was post a thread here but no one answer yet.
https://www.mrexcel.com/forum/excel-questions/1090889-formulas-macros-issue-my-work.html#post5243157

what i want in this file actually:
my partner will deploy schedule to my teammate once a week base on their availability. when i update it in sheet-schedule & sheet-availability,
1) i want to use a macro load in sheet-result column B-H: only time, without location, start & end time
2) sheet-result column J-P: return mapping result by schedule & availability
*i stuck in sheet-result column J&O for bella, she needs to end the shift before 20:00 on sunday and start after 13:45 on friday, but the outcome was "no-submit", i'd rather say it "wrong deploy by scheduler" instead of "no-submit" and then i can clearly find out and manually change it by myself.
column N for arkies, black by conditional formatting when cell is blank, can i change to "no-match"

thanks,
kelvin

 

[kelvin_9]

anyone got any ideas?