arrays - 用于嵌套ifs /数组的excel vba long

Question

我是新手，所以我不确定我会如何做到这一点。由于我的公式，它返回一个错误。我所有其他没有复杂数组/嵌套 if 的人都没有这个问题。

Dim XLoop As Long
Dim xSheetArray As Sheets
Dim xSheetObject As Worksheet
Set xSheetArray = ActiveWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3", "Sheet4"))

On Error Resume Next
For Each xSheetObject In xSheetArray
    For XLoop = 0 To 11
        xSheet.Range("B" & 5 + XLoop).Formula = "=IFS($A$1="MORNING",IFERROR(INDEX(Roster!$E$3:$E$14,SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$3:$E$14)+ISBLANK(Roster!$E$3:$E$14)=0,COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1,""),1)=IF(ISBLANK(Roster!$E$3:$E$14),"",COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1),ROW(Roster!$E$3:$E$14)-MIN(ROW(Roster!$E$3:$E$14))+1),1),MATCH(MIN(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$3:$E$14)+ISBLANK(Roster!$E$3:$E$14)>0,"",COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1)),INDEX(IF(ISBLANK(Roster!$E$3:$E$14),"",COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1),SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$3:$E$14)+ISBLANK(Roster!$E$3:$E$14)=0,COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1,""),1)=IF(ISBLANK(Roster!$E$3:$E$14),"",COUNTIF(Roster!$E$3:$E$14,"<"&Roster!$E$3:$E$14)+1),ROW(Roster!$E$3:$E$14)-MIN(ROW(Roster!$E$3:$E$14))+1),1),,1),0),1),""),$A$1="MID",IFERROR(INDEX(Roster!$E$15:$E$26,SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$15:$E$26)+ISBLANK(Roster!$E$15:$E$26)=0,COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1,""),1)=IF(ISBLANK(Roster!$E$15:$E$26),"",COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1),ROW(Roster!$E$15:$E$26)-MIN(ROW(Roster!$E$15:$E$26))+1),1),MATCH(MIN(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$15:$E$26)+ISBLANK(Roster!$E$15:$E$26)>0,"",COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1)),INDEX(IF(ISBLANK(Roster!$E$15:$E$26),"",COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1),SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$15:$E$26)+ISBLANK(Roster!$E$15:$E$26)=0,COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1,""),1)=IF(ISBLANK(Roster!$E$15:$E$26),"",COUNTIF(Roster!$E$15:$E$26,"<"&Roster!$E$15:$E$26)+1),ROW(Roster!$E$15:$E$26)-MIN(ROW(Roster!$E$15:$E$26))+1),1),,1),0),1),""),$A$1="EVENING",IFERROR(INDEX(Roster!$E$27:$E$38,SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$27:$E$38)+ISBLANK(Roster!$E$27:$E$38)=0,COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1,""),1)=IF(ISBLANK(Roster!$E$27:$E$38),"",COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1),ROW(Roster!$E$27:$E$38)-MIN(ROW(Roster!$E$27:$E$38))+1),1),MATCH(MIN(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$27:$E$38)+ISBLANK(Roster!$E$27:$E$38)>0,"",COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1)),INDEX(IF(ISBLANK(Roster!$E$27:$E$38),"",COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1),SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$27:$E$38)+ISBLANK(Roster!$E$27:$E$38)=0,COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1,""),1)=IF(ISBLANK(Roster!$E$27:$E$38),"",COUNTIF(Roster!$E$27:$E$38,"<"&Roster!$E$27:$E$38)+1),ROW(Roster!$E$27:$E$38)-MIN(ROW(Roster!$E$27:$E$38))+1),1),,1),0),1),""),$A$1="WEEKEND",IFERROR(INDEX(Roster!$E$39:$E$50,SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$39:$E$50)+ISBLANK(Roster!$E$39:$E$50)=0,COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1,""),1)=IF(ISBLANK(Roster!$E$39:$E$50),"",COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1),ROW(Roster!$E$39:$E$50)-MIN(ROW(Roster!$E$39:$E$50))+1),1),MATCH(MIN(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$39:$E$50)+ISBLANK(Roster!$E$39:$E$50)>0,"",COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1)),INDEX(IF(ISBLANK(Roster!$E$39:$E$50),"",COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1),SMALL(IF(SMALL(IF(COUNTIF($B$4:$B$" & 4 + xLoop & ",Roster!$E$39:$E$50)+ISBLANK(Roster!$E$39:$E$50)=0,COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1,""),1)=IF(ISBLANK(Roster!$E$39:$E$50),"",COUNTIF(Roster!$E$39:$E$50,"<"&Roster!$E$39:$E$50)+1),ROW(Roster!$E$39:$E$50)-MIN(ROW(Roster!$E$39:$E$50))+1),1),,1),0),1),""))"
    Next XLoop
Next xSheetObject

标签： arraysexcelvbaloopsnested