Error 1004 when VBA paste code to excel

Thank you for answer.
i don't know why but i cannot add attachment or even picture
so this my data, please use ctrl+shift+enter and use "record macro"


in tab1 this formula is correct but vba wont put it into cell
in tab 2 I divide formula into 3 parts and separately work all, error comes out when I add 3th part instead of "F"

shift1	shift2	time	formula
08:00-16:00	22:00-05:30	08:00-05:30	=TEXT(IF(MAX(IF(MID(A3:B3,6,1)="-",IF(MID(A3:B3,7,5)*1<MID(A3:B3,1,5)*1,MID(A3:B3,7,5)*1,"0"),"0"))=0,MAX(IF(MID(A3:B3,6,1)="-",MID(A3:B3,7,5)*1,"0")),MAX(IF(MID(A3:B3,6,1)="-",IF(MID(A3:B3,7,5)*1<MID(A3:B3,1,5)*1,MID(A3:B3,7,5)*1,"0"),"0"))),"HH:MM")
08:00-16:00	08:00-22:00	08:00-22:00
08:00-16:00	(blank)	08:00-16:00
tab2
08:00-16:00	22:00-05:30	F	=TEXT(IF(MAX(IF(MID(A8:B8,6,1)="-",IF(MID(A8:B8,7,5)*1<MID(A8:B8,1,5)*1,MID(A8:B8,7,5)*1,"0"),"0"))=0,"T","F"),"HH:MM")
08:00-16:00	22:00-05:30	16:30	=TEXT(MAX(IF(MID(A8:B8,6,1)="-",MID(A8:B8,7,5)*1,"0")),"HH:MM")
08:00-16:00	22:00-05:30	05:30	=TEXT(MAX(IF(MID(A10:B10,6,1)="-",IF(MID(A10:B10,7,5)*1<MID(A10:B10,1,5)*1,MID(A10:B10,7,5)*1,"0"),"0")),"HH:MM")

Originally Posted by TMS

Note that "0" is a text string...

This is why i put *1 after fragment of my string

The main question is why vba not allow in first IF function "if false".

See how this goes
Uses sendkeys so its not ideal but using vba to set the formula array appears to have some limitations.
the macro 1 is just your formula being passed to shg's macro.

Sub Macro1()
'
' Macro1 Macro
'
 InsertArrayFormula Range("D4"), "=TEXT(IF(MAX(IF(MID(R[6]C[-7]:R[6]C[-6],6,1)=""-"",IF(MID(R[6]C[-7]:R[6]C[-6],7,5)*1<MID(R[6]C[-7]:R[6]C[-6],1,5)*1,MID(R[6]C[-7]:R[6]C[-6],7,5)*1,""0""),""0""))=0,MAX(IF(MID(R[6]C[-7]:R[6]C[-6],6,1)=""-"",MID(R[6]C[-7]:R[6]C[-6],7,5)*1,""0"")),MAX(IF(MID(R[6]C[-7]:R[6]C[-6],6,1)=""-"",IF(MID(R[6]C[-7]:R[6]C[-6],7,5)*1<MID(R[6]C[-7]:R[6]C[-6],1,5)*1,MID(R[6]C[-7]:R[6]C[-6],7,5)*1,""0""),""0""))),""HH:MM"")"

End Sub

Function InsertArrayFormula(r As Range, _
                            sFrm As String, _
                            Optional ByVal sFmt As String = "") As Boolean
        ' shg 2009
    ' Inserts the R1C1 array formula sFrm into r
    ' The VBE CANNOT have focus when this runs!
    
    Dim iRef        As XlReferenceStyle
    Dim rSel        As Range
 
    On Error GoTo Oops
 
    With Application
        Set rSel = ActiveWindow.RangeSelection
        iRef = .ReferenceStyle
        .ReferenceStyle = xlR1C1
        .ScreenUpdating = False
        With r.Areas(1)
            ' Can't put an array formula in cells that are
            ' not either all locked or all unlocked, so …
            .Locked = .Cells(1).Locked
            
            ' Cache the number format, set to text, insert formula, restore format
            If Len(sFmt) = 0 Then sFmt = .NumberFormat
            .NumberFormat = "@"
            .Value = sFrm
            .NumberFormat = sFmt
            
            Application.Goto .Cells
        End With
 
        DoEvents
        .SendKeys "{F2}^+~"
        DoEvents

        .ReferenceStyle = iRef
        .Goto rSel
        .ScreenUpdating = True
    End With
 
    InsertArrayFormula = True
Oops:
    Exit Function
End Function

Thank you for this solution but is not "user friendly" i decide to put somewhere 1 cell with activated array formula and using Cells.Replace to change range.

It's just that your formula is too long. You can do something like this:

    Dim part1                       As String
    Dim part2                       As String

    part1 = "MID(A3:B3,6,1)"
    part2 = "MID(A3:B3,7,5)*1"
    ActiveCell.FormulaArray = _
    "=TEXT(IF(MAX(IF(""p1""=""-"",IF(""p2""<MID(A3:B3,1,5)*1,""p2"",""0""),""0""))=0,MAX(IF(""p1""=""-"",""p2"",""0"")),MAX(IF(""p1""=""-"",IF(""p2""<MID(A3:B3,1,5)*1,""p2"",""0""),""0""))),""HH:MM"")"
    ActiveCell.Replace what:="""p1""", replacement:=part1, lookat:=xlPart
    ActiveCell.Replace what:="""p2""", replacement:=part2, lookat:=xlPart

rorya - you won...

...my happiness! - is always something

Thanks
[Solved]