Issue creating macro button for very simple return

%3CLINGO-SUB%20id%3D%22lingo-sub-2778670%22%20slang%3D%22en-US%22%3EIssue%20creating%20macro%20button%20for%20very%20simple%20return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778670%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone.%20I%20am%20very%20new%20to%20Excel%20Macros%20and%20am%20trying%20to%20create%20a%20Macro%20that%20returns%20the%20number%20of%20rows%20in%20a%20given%20sheet%20where%20the%20value%20of%20column%20A%20is%20%26lt%3B%26gt%3B0.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20able%20to%20create%20a%20command%20button%20but%20don't%20know%20how%20to%20proceed%20to%20include%20the%20WorksheetFunction.CountIf%20section%20of%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESheet%20name%20%3D%20Test%3C%2FP%3E%3CP%3EColumn%20A%3C%2FP%3E%3CP%3EClick%20button%3C%2FP%3E%3CP%3EReturn%20number%20of%20rows%20on%20Test%20where%20value%20of%20column%20A%20is%20%26lt%3B%26gt%3B0.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2778670%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778702%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20creating%20macro%20button%20for%20very%20simple%20return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778702%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1164625%22%20target%3D%22_blank%22%3E%40JoaoPereira%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3EYourVariable%3DApplication.WorksheetFunction.CountIfs(Range(YourRange)%2C%20%22%26lt%3B%26gt%3B0%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3ESub%20CountValuesOnColumnA()%0A%20%20%20%20Dim%20YourOutput%20As%20Long%2C%20YourRange%20As%20Range%0A%20%20%20%0A%20%20%20%20With%20ActiveSheet.Range(%22A1%22).CurrentRegion%0A%20%20%20%20%20%20%20%20Set%20YourRange%20%3D%20.Offset(1).Resize(.Rows.Count%20-%201%2C%201)%0A%20%20%20%20%20%20%20%20YourOutput%20%3D%20Application.WorksheetFunction.CountIfs(YourRange%2C%20%22%26lt%3B%26gt%3B0%22)%0A%20%20%20%20End%20With%0A%20%20%20%20%0A%20%20%20%20MsgBox%20YourOutput%0A%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2778728%22%20slang%3D%22en-US%22%3ERe%3A%20Issue%20creating%20macro%20button%20for%20very%20simple%20return%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2778728%22%20slang%3D%22en-US%22%3EHello!%20Thank%20you%20so%20much.%20Now%20how%20do%20I%20make%20this%20result%20show%20up%20in%20a%20specific%20place%20in%20Excel%3F%20For%20example%2C%20I%20have%20a%20rectangle%20on%20Cell%20A1%20which%20I%20want%20this%20result%20to%20be%20displayed%20on%20after%20clicking%20the%20button.%20How%20do%20I%20make%20it%20show%20up%20there%3F%3C%2FLINGO-BODY%3E
New Contributor

Hello everyone. I am very new to Excel Macros and am trying to create a Macro that returns the number of rows in a given sheet where the value of column A is <>0. 

 

I have been able to create a command button but don't know how to proceed to include the WorksheetFunction.CountIf section of the code.

 

Sheet name = Test

Column A

Click button

Return number of rows on Test where value of column A is <>0.

 

 

3 Replies

@JoaoPereira 

 

 

YourVariable=Application.WorksheetFunction.CountIfs(Range(YourRange), "<>0")

 

Sub CountValuesOnColumnA()
    Dim YourOutput As Long, YourRange As Range
   
    With ActiveSheet.Range("A1").CurrentRegion
        Set YourRange = .Offset(1).Resize(.Rows.Count - 1, 1)
        YourOutput = Application.WorksheetFunction.CountIfs(YourRange, "<>0")
    End With
    
    MsgBox YourOutput
    
End Sub

 

Hello! Thank you so much. Now how do I make this result show up in a specific place in Excel? For example, I have a rectangle on Cell A1 which I want this result to be displayed on after clicking the button. How do I make it show up there?

@JoaoPereira 

To be honest, you dont need VBA for that.

You just need an ordinary formula like =COUNTIF(OFFSET($A$2,0,0,COUNTA(A:A)-1,1),"<>0")

Then you click over your retangle and type the address where the result of the formula is.

 

 

JulianoPetrukio_0-1632407389874.png

 

If you really need VBA (Not recommended)

Sub CountValuesOnColumnA()
    Dim YourOutput As Long, YourRange As Range
   
    With ActiveSheet.Range("A1").CurrentRegion
        Set YourRange = .Offset(1).Resize(.Rows.Count - 1, 1)
        YourOutput = Application.WorksheetFunction.CountIfs(YourRange, "<>0")
    End With
    ActiveSheet.Shapes(YourRetangleName).TextFrame.Characters.Text = YourOutput
End Sub