Ik heb een Matrix formule die iets langer is dan 255 karakters.
Alleen lukt het me niet om deze draaiend te krijgen.
Weet iemand hoe het wel kan
mvg
Kasper
Alleen lukt het me niet om deze draaiend te krijgen.
Weet iemand hoe het wel kan
Code:
Sub LongArrayFormula12()
Dim frmla1 As String
Dim frmla2 As String
Dim LastRow As Long
With ActiveSheet
LastRow = Sheets("Controle").Cells(.Rows.Count, "B").End(xlUp).Row
End With
'fullfrmla = "=IF(IFERROR(VLOOKUP(RC2&RC13,CHOOSE({1,2},Controle!R1C1:R1650C1&Controle!R1C12:R1650C12,Controle!R1C8:R1650C8),2,0),"""")=0,"""",IFERROR(TEXT(VLOOKUP(RC2&RC13,CHOOSE({1,2},Controle!R1C1:R1650C1&Controle!R1C12:R1650C12,Controle!R1C8:R1650C8),2,0),""D-M-JJJJ""),""""))"
frmla1 = "=IF(IFERROR(VLOOKUP(RC2&RC13,CHOOSE({1,2},Controle!R1C1:R" & LastRow & "C1&Controle!R1C12:R" & LastRow & "C12,Controle!R1C8:R" & LastRow & "C8),2,0),"""")=0,"""",XXXX)"
'frmla2 = "=IFERROR(TEXT(VLOOKUP(RC2&RC13,CHOOSE({1,2},Controle!R1C1:R" & LastRow & "C1&Controle!R1C12:R" & LastRow & "C12,Controle!R1C8:R" & LastRow & "C8),2,0),""D-M-JJJJ""),"""")"
frmla2 = "ALS.FOUT(TEKST(VERT.ZOEKEN($B1&$M1;KIEZEN({1\2};Controle!$A$1:$A$" & LastRow & "&Controle!$L$1:$L$" & LastRow & ";Controle!$H$1:$H$" & LastRow & ");2;0);""D-M-JJJJ"");"""")"
With ActiveSheet.Range("T1")
.FormulaArray = frmla1
.Replace What:="XXXX", Replacement:=frmla2, LookAt:=xlPart
End With
End Sub
mvg
Kasper