How do I get countif to work for all sheets in an array?

• Weird question, right?

The countif is in "Engine" sheet, cell"B3", but from what I can tell, I can only specify one sheet (currently =COUNTA('22'!B1:B5000)-1).
Should I even be trying to use countif?

My code is as follows:

Private Sub UserForm_Initialize()
Dim Ary As Variant

Ary = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")
Me.ComboBox1.List = Ary
End Sub

Sub Submit_Button_Click()
'When the Submit button is clicked
Dim TargetRow As Integer
Dim TargetSheet As Integer
TargetRow = Sheets("Engine").Range("B3").Value

'Day_Of_Month_Box
TargetSheet = Day_Of_Month_Box
' If your Sheets are Sheet1,Sheet2, etc ...
' And Day_Of_Month_Box equal to 1,2, etc...

With Sheets(Me.ComboBox1.Value).Range("A5")
.Offset(TargetRow, 1).Value = Assigned_To_Box
.Offset(TargetRow, 2).Value = O_Box
.Offset(TargetRow, 3).Value = Y_Box
.Offset(TargetRow, 4).Value = Assigned_By_Box
.Offset(TargetRow, 0).Value = Day_Of_Month_Box
End With

End Sub

• Hello and Welcome to the Forum :smile:

In your sheet Engine cell B3 you do need a formula to work out a total ...

However, what is your expected result for this cell ?

Do you need a 3D Count similar to a 3D Sum to scan your ' 31 day sheets ' ...?

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

• Hello again,

Regarding your Submit_Button ... you seem not to need the TargetSheet variable ...

Forgot to mention ... one the GREAT advantage of the OzGrid Forum ... is the possibility to attach a sample file ..:wink:

If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!