VBA CountIf function results in "0"

[PlutoQX]

Hello all,


nice to join this community

I have a question regarding the countif function in VBA form.


The actual situation

I have a master workbook(Wb) with a user form - file explorer (Screenshot 1) and a Sheet2 for the results(Screenshot 2)

I have a source workbook, which has a huge amount of data(14k rows) and is quite frankly a mess.. For my calculation only 2 columns of the source workbook are relevant: column 8 (True Dates) and colum 30 (numerical values).
Important note regarding column 8 : only the rows with the first of every month is relevant for me. Meaning: 01.01., 01.02.,01.03.,01.04. etc. Every other row should be ignored.

I use the Wb to search for the source workbook(wb.Source) to apply the following calculations:

Task 1: calculating the avarage value if certain conditions are met

Task 2: counting rows if the following conditions are met:

• If a row in wb.Source has the values in Column 8 = "01.01" and Column 30 is < 50 then count and enter the result in cell B8 in Sheet 2 of Wb after the whole wb,Source is checked.
• If a row in wbSource has the values Column 8= "01.01" and Column 30 is > 50 and < 100 then count and enter the result in cell B9 in Sheet 2 of Wb after the whole wb,Source is checked.
• If a row in wbSource has the values Column 8= "01.01" and Column 30 is > 100 then count and enter the result in cell B10 in Sheet 2 of Wb after the whole wb,Source is checked.

This should be applied for every month.


The results are then added to my Wb Sheet2.


My problems/questions

1. Regarding Task 2: My CountIf function results in "0" for every month and I dont knw why? See attached screenshots from my result sheet of Wb(Screenshot 2) and an example of rows of my wb.Source.(Screenshot 3)

2. Regarding Task 1: the calculation works so far, but how can I make sure that only the rows with the first of every month are considered and everything else ignored?

3. Is it possible to restrict the scope of the calculation in the sense that only the past 12 months are calculated, starting from a certain date(counting back). Meaning: To set a Date(Month/Year) in my user form and consider only the rows of the past 12 months for the calculation? If no date is set then "today" should be the starting point. I think the Const = YEAR has to be removed from the code, as there is definitely the possibility that the wb.Source contains rows from different years.




I would appreciate any help!





The code

Private Sub CommandButton1_Click() ' select file

    Dim fname As Variant
    With Me
        fname = Application.GetOpenFilename("Excel File(s) (*.xls*),*.xls*", , "Select FIle", , False)
        If fname <> "False" Then .TextBox1.Text = fname
    End With
End Sub


'calculate avarage

Private Sub CommandButton2_Click() ' update averages

     Const YEAR = 2019

    ' open source workbook
    Dim fname As String, wbSource As Workbook, wsSource As Worksheet
    fname = Me.TextBox1.Text

    If Len(fname) = 0 Then
       MsgBox "No file selected", vbCritical, "Error"
       Exit Sub
    End If

    Set wbSource = Workbooks.Open(fname, False, True) ' no link update, read only
    Set wsSource = wbSource.Sheets("Sheet1") ' change to suit

    Dim wb As Workbook, ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet2") '

    ' scan down source workbook calc average
    Dim iRow As Long, lastRow As Long
    Dim sMth As String, iMth As Long
    Dim count(12) As Long, sum(12) As Long

    lastRow = wsSource.Cells(Rows.count, 1).End(xlUp).Row
    For iRow = 1 To lastRow

        If IsDate(wsSource.Cells(iRow, 8)) _
            And IsNumeric(wsSource.Cells(iRow, 30)) Then

            iMth = Month(wsSource.Cells(iRow, 8))   ' col H
            sum(iMth) = sum(iMth) + wsSource.Cells(iRow, 30) ' Col AD
            count(iMth) = count(iMth) + 1 '

        End If
    Next
    

'calculate number of rows (CountIf)


Dim x As Long
Dim m As Variant

For x = 1 To 12

    m = "01." & Format(x, "00")


    ws.Cells(8, 1 + x) = _
    Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
    wsSource.Columns(30), "<=" & 50)

    ws.Cells(9, 1 + x) = _
    Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
        wsSource.Columns(30), ">" & 50, wsSource.Columns(30), "<=" & 100)

    ws.Cells(10, 1 + x) = _
        Application.WorksheetFunction.CountIfs(wsSource.Columns(8), m & "*", _
        wsSource.Columns(30), ">" & 100)
        
        
        


Next x
    
    
    

    ' close source worbook no save

    wbSource.Close False


 ' update Sheet 2 with averages

    With ws.Range("A3")
    For iMth = 1 To 12
        .Offset(0, iMth - 1) = MonthName(iMth) & " " & YEAR
        If count(iMth) > 0 Then
            .Offset(1, iMth - 1) = sum(iMth) / count(iMth)
            .Offset(1, iMth - 1).NumberFormat = "0.0"
        End If
    Next
    End With

    Dim msg As String
    msg = iRow - 1 & " rows scanned in " & TextBox1.Text
    MsgBox msg, vbInformation, "Sheet 2 updated"

End Sub