Problem executing VBA code using a button and also getting an output as '0" RRS feed

  • Question

  • Hello Experts

    I am new to vba and very keen in understanding it better. I have written below code around countifs

    Issue 1 - When i debug, i do not get any error, instead i get output as '0' even though my input file looks as below. However if i copy paste the keyword 'Walkthrough' in the code and execute it, i would get the correct output i.e 2

    Issue 2 - If I assign a button to this macro and run, i would get an output as '0'

    Input File

    Walkthrough   Not Started

    Walkthrough   Not Started

    Actual Code

    Sub countif_test()

    Dim type_of_test As Long
    Dim Walk As Worksheet
    Dim source As Worksheet

    Set source = Worksheets("Archer Search Report")
    Set Walk = Worksheets("SOX_Dashboard_Walkthrough_Stat")

    type_of_test = Application.WorksheetFunction.CountIfs(Range("A2:A3"), "Interim", Range("B2:B3"), "Not Started")

    Walk.Range("C2") = type_of_test

    End Sub

    Request you to assist in regard this. Thanks in advance.

    Tuesday, March 13, 2018 8:15 AM

All replies

  • You define a Worksheet variable source but you don't do anything with it. Did you intend the ranges A2:A3 and B2:B3 to be on the Archer Search Report sheet? If so, use

    type_of_test = Application.WorksheetFunction.CountIfs(source.Range("A2:A3"), "Interim", source.Range("B2:B3"), "Not Started")

    Regards, Hans Vogelaar (

    Tuesday, March 13, 2018 9:13 AM