macro to run array formula

[kelvin_9]

hi all,
i have my formula in cell original, but i want to change and run in macro now. unfortunately it exceeds over 255 limitation of array formula in macro.
(=IF(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0))="","",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),7,5)),0)),"hh:mm")))
how can i do it with a macro? or any other way that i can spilt my original one to 2 formulas and then still run in macro?

my request: xx:xx - xx:xx on time format, start & end time only
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Time[/TD]
[TD]outcome[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ada[/TD]
[TD]13:00-22:45[/TD]
[TD]13:00 - 22:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ted[/TD]
[TD]13:15-15:00[/TD]
[TD]13:15 - 22:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]15:00-21:15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]21:15-22:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]22:00-22:45[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Kathy[/TD]
[TD]9:00-12:15[/TD]
[TD]09:00 - 18:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]12:15-18:30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

thanks for doing this
kelvin

 

[pgc01]

Hi kelvin

The rule for spliting the array formula is to make sure that you respect the formula syntax.

If I understand correctly you formula has the form:

=IF(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0))="","",TEXT(Expression1)&" - "&TEXT(Expression2))

You can use a similar formula that respects the formula syntax, like for ex.:

=IF(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0))="","",11111&" - "&22222)

This is a formula that you can insert in a cell and that is accepted as a valid formula.

You can then replace the 11111 and the 22222 by your respective expressions.

Remark:

You can also look here:

https://www.mrexcel.com/forum/excel-questions/687125-long-array-formulas-vba.html

especially posts #2 and #4

 

[kelvin_9]

thanks PGC

Sub Test()
Dim rDate As Range, rDest As Range
Set rDate = Range("INDEX(schedule!$C:$C,MATCH(result!$A71,schedule!$A:$A,0))=""")
Set rDest = Range("a2")


rDest.FormulaArray = "=IF(1111,"",TEXT(MIN(IFERROR(TIMEVALUE(LEFT(2222:INDEX(schedule!$C:$C,MATCH(result!$A72,schedule!$A:$A,0)-1),5)),1)),"hh:mm")&" - "&TEXT(MAX(IFERROR(TIMEVALUE(MID(3333:INDEX(schedule!$C:$C,MATCH(result!$A72,schedule!$A:$A,0)-1),7,5)),0)),"hh:mm"))"
rDest.Replace "1111", "(" & rDate.Address & ")", LookAt:=xlPart
rDest.Replace "2222", "(" & rDate.Address & ")", LookAt:=xlPart
End Sub


looks like this? seems have some error even not yet run

 

[pgc01]

HI

As I said you have to use and intermediate formula that respects the formula syntax.
You used

1111:INDEX()

which is not a valid syntax. Also inside a double quoted string you have to double up the double quotes

If you have

="abc"&"cde"

you must use:

s = "=""abc""&""cde"""

The code you posted doesn't respect neither the formula syntax not the vba building of strings.

Like I posted, if I understood correctly, your formula is similar to:

=IF(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0))="","",1111&" - "&2222))

where 1111 stands for your first Text() expression

1111: TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),5)),1)),"hh:mm")

and 2222 stands for your second Text() expression

2222: TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),7,5)),0)),"hh:mm")

I'd use this to insert your formula, like:

Sub Test()
Dim rDate As Range, rDest As Range
Dim sText1 As String, sText2 As String

Set rDate = Range("a1")
Set rDest = Range("a2")

sText1 = "TEXT(MIN(IFERROR(TIMEVALUE(LEFT(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),5)),1)),""hh:mm"")"
sText2 = "TEXT(MAX(IFERROR(TIMEVALUE(MID(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0)):INDEX(schedule!$C:$C,MATCH(result!$A52,schedule!$A:$A,0)-1),7,5)),0)),""hh:mm"")"

rDest.FormulaArray = "=IF(INDEX(schedule!$C:$C,MATCH(result!$A51,schedule!$A:$A,0))="""","""",1111&""-""&2222)"
rDest.Replace "1111", sText1, LookAt:=xlPart
rDest.Replace "2222", sText2, LookAt:=xlPart

End Sub

Write a date in A1 and run this code. See if the formula in A2 is what you expect.

 

[kelvin_9]

i must apologize and thank you what you did for me
THANK YOU PGC!

another if i want to autofill, where should i put this?
​

Selection.AutoFill Destination:=Range("b2:b" & Range("a" & Rows.Count).End(xlUp).Row)

​

 

[pgc01]

I'm glad it helped.

As for the AutoFill, assuming you have a formula in B2 and you want to fill down until the last populated row in column A, you can use:

Range("B2").AutoFill Destination:=Range("b2:b" & Range("a" & Rows.Count).End(xlUp).Row)

If you have a formula in B2 and the last populated cell in column A is A10, you'll get the formula in B2:B10.

 

[kelvin_9]

I'm glad it helped.

As for the AutoFill, assuming you have a formula in B2 and you want to fill down until the last populated row in column A, you can use:

Range("B2").AutoFill Destination:=Range("b2:b" & Range("a" & Rows.Count).End(xlUp).Row)

If you have a formula in B2 and the last populated cell in column A is A10, you'll get the formula in B2:B10.

life saver, IT IS PREFECT
thanks again PGC

 

[pgc01]

You're welcome. Thanks for the feedback.