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

ShutUpCrime · Apr 20th 2019

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

Unload ATC_Tracker
End Sub

**Carim** · Apr 21st 2019

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 ' ...?

**Carim** · Apr 21st 2019

Hello again,

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

Code

Sub Submit_Button_Click()
'When the Submit button is clicked
Dim TargetRow As Integer
TargetRow = Sheets("Engine").Range("B3").Value
  If Me.ComboBox1 <> "" Then
      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 If
Unload ATC_Tracker
End Sub

Display More

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

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

Participate now!

Share