Adding CountIfs to an If Statement in a Loop?

[cosmic_chelonian]

I have a fairly extensive Select Case statement to translate data.

What I am trying to do for Case 20 is count how many cells in the adjacent 7 columns are >0. Basically, I am trying to count how many days someone is scheduled to work based on how many hours they are scheduled that day.

    For r = LBound(var1, 1) To UBound(var1, 1)
        For c = LBound(Cols) To UBound(Cols)

                Case Is = 20
                    If var1(r, 1) = "yes" Then
                            Result = "7"
                    ElseIf var1(r, 1) = "no" Then
                    Result = "?????"
                    End If

I made a table that shows the end result I need. Essentially, the result would need to be a =COUNTIF(B2:H2,">0") , =COUNTIF(B3:H3,">0"), in the last column for each row with a "No" in the first column. I am just stumped on how to fit that into an if statement where it is looping through potentially hundreds of rows.

Schedule?​	Sunday​	Monday​	Tuesday​	Wednesday​	Thursday​	Friday​	Saturday​	Days Per Week​
No​	0​	8​	8​	8​	8​	8​	0​	5​
No​	0​	0​	8​	8​	8​	8​	0​	4​
Yes​								7​

I am very new and self-taught so my vocabulary might not make any sense and my code is not pretty. I am absolutely stuck about how to go about automating the total of days worked per week for each row.

ANY tips or help would be greatly appreciated. Thank you!

 

[Eric W]

You'll probably need to create a loop. Something like:

For r = LBound(var1, 1) To UBound(var1, 1)
        For c = LBound(Cols) To UBound(Cols)

                Case Is = 20
                    If var1(r, 1) = "yes" Then
                            Result = "7"
                    ElseIf var1(r, 1) = "no" Then
                    Result = 0
                    For cc = 2 to 8
                        If var1(r, cc) > 0 Then Result = Result + 1
                    Next cc
                    End If

 

[cosmic_chelonian]

You'll probably need to create a loop. Something like:

For r = LBound(var1, 1) To UBound(var1, 1)
        For c = LBound(Cols) To UBound(Cols)

                Case Is = 20
                    If var1(r, 1) = "yes" Then
                            Result = "7"
                    ElseIf var1(r, 1) = "no" Then
                    Result = 0
                    For cc = 2 to 8
                        If var1(r, cc) > 0 Then Result = Result + 1
                    Next cc
                    End If

Perfect!!! Exactly what I needed! Thank you so much!

 

[Eric W]

Happy to help! Thanks for the feedback. ?