excel vba long for nested ifs/array

Question

I newer to excel so I'm not sure how I would do this. It returns an error as a result of my formula. All my other ones that don't have complicated arrays/nested ifs don't have this issue.

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

You need to double up your quotes: https://stackoverflow.com/questions/9024724/how-do-i-put-double-quotes-in-a-string-in-vba — BigBen, Jan 24 '20 at 15:14
Should `xSheet.Range()` be `xSheetObject.Range()`? You loop through sheets and have no references to the `xSheetObject` within your loop — Cyril, Jan 24 '20 at 15:21
Remove `On Error Resume Next`, it's part of the problem. As is the missing `Option Explicit` at the top of your module. — Mathieu Guindon, Jan 24 '20 at 15:28
@BigBen It says my syntax is too long. I double quoted everything in the quotes. — Ahrion Gallegos, Jan 24 '20 at 15:47
Type the formula in an excel cell **manually**. See if it works. If it does then share the exact formula here. We will take it from there... — Siddharth Rout, Jan 24 '20 at 16:00
Given the complexity of that formula, and that you're already using VBA, why not do some logic in VBA itself, and then save the value to that cell instead? — BruceWayne, Jan 24 '20 at 17:20
Thanks for the help everyone. I ended up just doing a record macro to achieve the same thing, and then I copied the macro to my formula and voila! — Ahrion Gallegos, Jan 24 '20 at 18:51

excel vba long for nested ifs/array

0 Answers0