SOLVED

Issue creating macro button for very simple return

Copper 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
best response confirmed by allyreckerman (Microsoft)
Solution

@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

 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@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

 

View solution in original post