Compiling Error Syntax Error

[Pookiemeister]

Private Sub UserForm_Activate()

    Worksheets("Work Orders").Activate
    
'**********************************************************************************************************************************
    'Totals ALL Processing Work Orders
    Me.Label15.Caption = Application.WorksheetFunction.CountIf(Range("L:L"), "TPM Processing Technician (P4TPMPRO)")
    
    'Totals Closed Processing Work Orders
     me.Label31.Caption = application.WorksheetFunction.CountA(choosecols(filter(Work_Orders,(work_orders[main work center]=G139)*(Work_Orders[Order Status]="Released"),""),18))

End Sub

This error occurs on this line of code:

me.Label31.Caption = application.WorksheetFunction.CountA(choosecols(filter(Work_Orders,(work_orders[main work center]=G139)*(Work_Orders[Order Status]="Released"),""),18))

This code actually works inside a spreadsheet cell but not in VBA. "Work_Orders" is the name of the table. Thanks

=CountA(choosecols(filter(Work_Orders,(work_orders[main work center]=G139)*(Work_Orders[Order Status]="Released"),""),18))

 

[Pookiemeister]

So if what I just read was correct on another sites vba forum, choosecols does not work in vba, yet, but it was suggested to use index & match instead. So is there a way to convert the code above to work with index and match. Thank you.

 

[Eric W]

I don't know if ChooseCols works in VBA yet, but you have a larger problem. If you use WorksheetFunction.xxx, then you have to code it using vba syntax, NOT sheet formula syntax. You just put the interior part of the formula inside the WorksheetFunction.CountA function, but the entire formula needs to be converted to VBA syntax.

This is possible, but instead, I think it would be easier to use the Evaluate function in VBA, which allows you to use formula syntax. Try:

me.Label31.Caption = Evaluate("=COUNTA(FILTER(Work_Orders[Col18],(Work_Orders[Main Work Center]=G139)*(Work_Orders[Order Status]=""Released""),""""))")

Note the doubled double quotes. Also I removed the CHOOSECOLS with the expedient of using the column name in the FILTER, but you should be able to use CHOOSECOLS if you want.

 

[Pookiemeister]

I don't know if ChooseCols works in VBA yet, but you have a larger problem. If you use WorksheetFunction.xxx, then you have to code it using vba syntax, NOT sheet formula syntax. You just put the interior part of the formula inside the WorksheetFunction.CountA function, but the entire formula needs to be converted to VBA syntax.

This is possible, but instead, I think it would be easier to use the Evaluate function in VBA, which allows you to use formula syntax. Try:

me.Label31.Caption = Evaluate("=COUNTA(FILTER(Work_Orders[Col18],(Work_Orders[Main Work Center]=G139)*(Work_Orders[Order Status]=""Released""),""""))")

Note the doubled double quotes. Also I removed the CHOOSECOLS with the expedient of using the column name in the FILTER, but you should be able to use CHOOSECOLS if you want.

I don't know if ChooseCols works in VBA yet, but you have a larger problem. If you use WorksheetFunction.xxx, then you have to code it using vba syntax, NOT sheet formula syntax. You just put the interior part of the formula inside the WorksheetFunction.CountA function, but the entire formula needs to be converted to VBA syntax.

This is possible, but instead, I think it would be easier to use the Evaluate function in VBA, which allows you to use formula syntax. Try:

me.Label31.Caption = Evaluate("=COUNTA(FILTER(Work_Orders[Col18],(Work_Orders[Main Work Center]=G139)*(Work_Orders[Order Status]=""Released""),""""))")

Note the doubled double quotes. Also I removed the CHOOSECOLS with the expedient of using the column name in the FILTER, but you should be able to use CHOOSECOLS if you want.

 

[Pookiemeister]

Me.Label31.Caption = Evaluate("=COUNTA(FILTER(Work_Orders[Col18],(Work_Orders[Main Work Center]=G139)*(Work_Orders[Order Status]=""Released""),""""))")

produces a runtime error 13: Type mismatch.

Thank you for the quick reponse

 

[Eric W]

Did you change Col18 to the actual column heading?

 

[Pookiemeister]

G139 in the original code is a range reference. It is not in a column per say, It is referencing the value in the range G139. If the code above worked I then would of moved on to reuse that same code but reference the value in G140 and so on. Thank you.

 

[Eric W]

G139 in the original code is a range reference. It is not in a column per say, It is referencing the value in the range G139. If the code above worked I then would of moved on to reuse that same code but reference the value in G140 and so on. Thank you.

You can adjust that range dynamically. And while I'm thinking about it, you should probably add a sheet reference to G139, in case you run the macro when G139 is not on the active sheet. So something like:

For ParameterRow = 139 to 145
    Me.Label31.Caption = Evaluate("=COUNTA(FILTER(Work_Orders[Col18],(Work_Orders[Main Work Center]='Data Sheet'!G" & ParameterRow & ")*(Work_Orders[Order Status]=""Released""),""""))")
Next ParameterRow