i was amended a formula here and planning to run in macro, but i got stuck
is it mean one more range need to assign to run the new formula?
is it mean one more range need to assign to run the new formula?
Code:
Sub Macro003()'
' Macro003 Macro
'
'
Sheets("result").Select
Dim rDate As Range, rDest As Range
Dim sText1 As String, sText2 As String
Set rDate = Range("c50")
Set rDest = Range("c51")
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
Range("c51").AutoFill Destination:=Range("c51:c" & Range("a" & Rows.Count).End(xlUp).Row)
Range("b51").Select
ActiveCell.FormulaR1C1 = _
"=IFERROR((VLOOKUP(RC1,'availability'!C1:C23,5,FALSE)),"""")"
Range("b51").AutoFill Destination:=Range("b51:b" & Range("a" & Rows.Count).End(xlUp).Row)
End Sub
Code:
=IF(INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0))="","",if(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")="00:00",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")))