Results 1 to 4 of 4

Thread: spliting the array formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default spliting the array formula

    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?

    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")))

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: spliting the array formula

    Code:
    Sub Test()Dim rDate As Range, rDest As RangeDim sText1 As String, sText2, sText3 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"")=""00:00"""
    sText2 = "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"")"
    sText3 = "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))="""","""",if(1111,INDEX(SCHEDULE!$C:$C,MATCH(RESULT!$A51,SCHEDULE!$A:$A,0)),2222&""-""&3333)"
    rDest.Replace "1111", sText1, LookAt:=xlPart
    rDest.Replace "2222", sText2, LookAt:=xlPart
    rDest.Replace "3333", sText3, LookAt:=xlPart
    
    
    End Sub
    Last edited by kelvin_9; Sep 20th, 2019 at 09:00 AM.

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: spliting the array formula

    anyone know?

  4. #4
    Board Regular
    Join Date
    Mar 2015
    Posts
    156
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: spliting the array formula

    no one know?

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •