Count and CountIf Confusion VBA

Question

I have a for loop that iterates through each sheet in my workbook. Each sheet is identical for most purposes. I am making an overview sheet in my workbook to express the results from the data in the other sheets(the ones that are identical).

There are 11 vehicles, each with their own sheet that has data from a test from each day. On any given day there can be no tests, or all the way to 30,000 tests. The header of each column in row 47 states the date in a "06/01/2021 ... 06/30/2021" format. The data from each iteration of the test will be pasted in the column of the correct date starting at row 49.

So what my overview page needs to display is the data from the previous day. In one cell on my overview there is a formula for obtaining just the number of the day of the month like 20 or 1 etc. Using this number, the number of the day is the same as the column index that the previous day's data will be in conveniently. So in my for loop I want to have a table that has the name of each sheet in column B, in column C I want to display the total number of tests done in that day(not the sum of all the data), in column D I need the number of tests with a result of 0, in column E I need the number of tests that have a result above the upper tolerance, and in column F I need the number of tests that have a result below the lower tolerance minus the result in column D.

I've been playing with the Application.WorksheetFunction.Count and CountIf functions but I keep getting 0 for every single cell. I made two arrays for the upper and lower tolerance values which are type Longs called UTol and LTol respectively. TabList is a public array that has each of the sheet names for the vehicles stored as strings. finddate is an integer that reads in Today's day number which is yesterday's column index. I've included a picture of the table in question and my for loop code:

    
    finddate = Worksheets("Overview").Range("A18").Value
    
    For TabNRow = 1 To 11
        startcol = 3
        Worksheets(TabList(TabNRow)).Activate
        Debug.Print (TabList(TabNRow))
        
        'Total number of tests done that day
        Worksheets("Overview").Cells(startrow, startcol).Value = Application.WorksheetFunction.Count(Range(Cells(49, finddate), Cells(30000, finddate)))
        startcol = 4
        
        'Total number of 0 results, this used to get the number of 0's from each sheet but that row has since been deleted
        Worksheets("Overview").Cells(startrow, startcol).Value = Worksheets(TabList(TabNRow)).Cells(48, finddate).Value
        startcol = 5
        
        'Total number of results above tolerance
        Worksheets("Overview").Cells(startrow, startcol).Value = Application.WorksheetFunction.CountIf(Range(Cells(49, finddate), Cells(30000, finddate)), ">" & UTol(TabNRow))
        startcol = 6
        
        'Total number of results below tolerance
        Worksheets("Overview").Cells(startrow, startcol).Value = Application.WorksheetFunction.CountIf(Range(Cells(49, finddate), Cells(30000, finddate)), "<" & LTol(TabNRow))
        startrow = startrow + 1
    Next TabNRow
    ```

Tim Williams · Answer 1 · 2021-07-20T18:42:50.377

0

No need to select/activate sheets when referencing them. See: How to avoid using Select in Excel VBA

Something like this should work:

Dim wsOv As Worksheet, wsData As Worksheet, rngData As Range

Set wsOv = ThisWorkbook.Worksheets("Overview")
finddate = wsOv.Range("A18").Value

For TabNRow = 1 To 11
    
    Set wsData = ThisWorkbook.Worksheets(TabList(TabNRow))
    Set rngData = wsData.Range(wsData.Cells(49, finddate), _
                               wsData.Cells(Rows.Count, finddate).End(xlUp))
    
    Debug.Print wsData.Name, rngData.Address 'edited
    
    With wsOv.Rows(2 + TabNRow)
        .Columns("C").Value = Application.Count(rngData)
        .Columns("D").Value = Application.CountIf(rngData, 0)
        .Columns("E").Value = Application.CountIf(rngData, ">" & UTol(TabNRow))
        .Columns("F").Value = Application.CountIf(rngData, "<" & LTol(TabNRow)) _
                               - .Columns("D").Value
    End With
    
Next TabNRow

edited Jul 20 '21 at 18:42

answered Jul 20 '21 at 16:33

Tim Williams

154,628
8
97
125

When I tried running this code, it gave me the value 1 for total number of tests in column C for every sheet, a value of 1 for every sheet for the number of 0's, and a value of 0 for every sheet for both columns E and F for the tolerances. – Tomastep Jul 20 '21 at 18:00
See the edit above on the Debug.Print line - if you run the code do the column ranges look correct? – Tim Williams Jul 20 '21 at 18:43
No. Its not calculating values correctly from my data. I found a way to just do it by hand in Excel and use INDIRECT to obtain the column letter from the numerical date. – Tomastep Jul 21 '21 at 14:12

Count and CountIf Confusion VBA

1 Answers1