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



    Unload ATC_Tracker
    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!