Ask Your Question
0

How Do I Use Countifs In A Calc Macro?

asked 2016-11-20 14:12:42 +0200

Solway-Sam gravatar image

I've been trying to change an Excel macro into a Calc one. The puropse is to count rows of data only when there are certain entries in three different columns. Here's my original code where WordCol, DirectionCol and NumberCol are integers refering to the numbers of the columns I want to use and Sheet1 = ThisComponent.Sheets(0):

TotalCount = WorksheetFunction.CountIfs(Sheet1.Columns(WordCol.Column), "Yes", Sheet1.Columns(DirectionCol.Column), "Up", Sheet1.Columns(NumberCol.Column), 1)

I found this question about using optimalWidth in a macro and can use this code to change the width of the columns (it works as expected for all three columns):

Sheet1.getColumns().getByIndex(WordCol).OptimalWidth = true

Using that style of column selection in my original code still gives an error "BASIC runtime error. Object variable not set.":

TotalCount = WorksheetFunction.CountIfs(Sheet1.getColumns().getByIndex(WordCol), "Yes", Sheet1.getColumns().getByIndex(DirectionCol), "Up", Sheet1.getColumns().getByIndex(NumberCol), 1)

I guess I must be using countifs wrong but couldn't find any information on how to use it in a macro. So, how do I use countifs in a calc macro?

edit retag flag offensive close merge delete

Comments

I changed my sub using the answer given by Ratslinger. It works for the first "if" but how do I use multiple criteria? Here's the bit of Ratslinger's code I changed:

    dim aArgument(5) as variant
    aArgument(0)=myRange1
    aArgument(1)="Yes"
    aArgument(2)=myRange2
    aArgument(3)="Up"
    aArgument(4)=myRange3
    aArgument(5)=1
End sub

This gives the error: "BASIC runtime error. An exception occurred Type: com.sun.star.uno.RuntimeException Message: ."

Solway-Sam gravatar imageSolway-Sam ( 2016-11-20 18:04:04 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2016-11-20 16:53:46 +0200

Ratslinger gravatar image

updated 2016-11-20 19:06:54 +0200

From your question it is not exactly clear what you are searching for. Here is a macro using COUNTIFS to find the number of cells containing a value of "20" or more in a range of "A1:A10" on sheet "0" and displays the total:

Sub TestIf
    dim oFunction as variant
    oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Sheet0")
    myRange = oSheet.getCellRangeByName( "A1:A10" )
    dim aArgument(1) as variant
    aArgument(0)=myRange
    aArgument(1)=">=20"
    TotalCount = oFunction.callFunction( "COUNTIFS", aArgument() )
    MsgBox TotalCount
End sub

For more information on macros, the most complete reference/guide in one document is Open Office Macros Explained (OOME) by Andrew Pitonyak (click here). You can download the PDF version there.

Edit: the code using your conditions with data in columns A, B & C rows 1-6:

Sub TestIf
    dim oFunction as variant
    oFunction = createUnoService("com.sun.star.sheet.FunctionAccess")
    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Sheet0")
    dim aArgument(5) as variant
    myRange = oSheet.getCellRangeByName( "A1:A6" )
    aArgument(0)=myRange
    aArgument(1)="Yes"
    myRange = oSheet.getCellRangeByName( "B1:B6" )
    aArgument(2)=myRange
    aArgument(3)="Up"
    myRange = oSheet.getCellRangeByName( "C1:C6" )
    aArgument(4)=myRange
    aArgument(5)="1"
    TotalCount = oFunction.callFunction( "COUNTIFS", aArgument() )
    MsgBox TotalCount
End sub

You were close, just needed to specify the additional ranges.

edit flag offensive delete link more

Comments

Thanks a lot for the help. Imagine three columns with data like this:

Yes Up      1
No  Up      0
Yes Down    1
Yes Up      0
Yes Up      1
No  Down    1

I'd like to count the total number of rows with "Yes" in the first column, "Up" in the second and "1" in the third.

Solway-Sam gravatar imageSolway-Sam ( 2016-11-20 17:53:02 +0200 )edit

You're a genius, thanks! :-)

Solway-Sam gravatar imageSolway-Sam ( 2016-11-20 20:26:20 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-11-20 14:12:42 +0200

Seen: 608 times

Last updated: Nov 20 '16