I have a macro that uses the DCOUNTA formula repeatedly with a routine that
copies the results into a summary spreadsheet (not a very elegant solution.)
I am trying build a replacement that does not require me to visually show the
copying.

Here is my question: Is there a way to define the Criteria Array in the
formula itself instead of in a range on a spreadsheet? Something like...

=DCOUNTA('database'!A:H,1,{"Country","China","Stat us","Complete"})

The problem is that I do not know how to specify the two dimensions of this
array. I tried specifying the individual cells of a 2x2 array in a macro,
but I get a "data type mismatch" error.

Dim criteria(1, 1) As String
criteria(0, 0) = """Country"""
criteria(0, 1) = """Mid-year Status"""
criteria(1, 0) = """China"""
criteria(1, 1) = """Complete"""
ActiveCell.Value = WS.Evaluate("=DCOUNTA('database'!A:H,1," & criteria &
")")

Ideas?

Thank you.

Why don't you use simply
Application.ScreenUpdating = False
at the start of your code if you only want eliminate displaying changes?
At the end of the code apply
Application.ScreenUpdating = True

Regards,
Stefi

€žJLBennett€� ezt Ã*rta:

I have a macro that uses the DCOUNTA formula repeatedly with a routine that
copies the results into a summary spreadsheet (not a very elegant solution.)
I am trying build a replacement that does not require me to visually show the
copying.

Here is my question: Is there a way to define the Criteria Array in the
formula itself instead of in a range on a spreadsheet? Something like...

=DCOUNTA('database'!A:H,1,{"Country","China","Stat us","Complete"})

The problem is that I do not know how to specify the two dimensions of this
array. I tried specifying the individual cells of a 2x2 array in a macro,
but I get a "data type mismatch" error.

Dim criteria(1, 1) As String
criteria(0, 0) = """Country"""
criteria(0, 1) = """Mid-year Status"""
criteria(1, 0) = """China"""
criteria(1, 1) = """Complete"""
ActiveCell.Value = WS.Evaluate("=DCOUNTA('database'!A:H,1," & criteria &
")")

Ideas?

Thank you.

Thanks for your help, Stefi!

Silly me. It had been awhile since I had built a macro where this mattered.
I forgot about this option and dived in for a frontal assault.

Regards,
-Jeff

"Stefi" wrote:

Why don't you use simply
Application.ScreenUpdating = False
at the start of your code if you only want eliminate displaying changes?
At the end of the code apply
Application.ScreenUpdating = True

Regards,
Stefi

€žJLBennett€� ezt Ã*rta:

I have a macro that uses the DCOUNTA formula repeatedly with a routine that
copies the results into a summary spreadsheet (not a very elegant solution.)
I am trying build a replacement that does not require me to visually show the
copying.

Here is my question: Is there a way to define the Criteria Array in the
formula itself instead of in a range on a spreadsheet? Something like...

=DCOUNTA('database'!A:H,1,{"Country","China","Stat us","Complete"})

The problem is that I do not know how to specify the two dimensions of this
array. I tried specifying the individual cells of a 2x2 array in a macro,
but I get a "data type mismatch" error.

Dim criteria(1, 1) As String
criteria(0, 0) = """Country"""
criteria(0, 1) = """Mid-year Status"""
criteria(1, 0) = """China"""
criteria(1, 1) = """Complete"""
ActiveCell.Value = WS.Evaluate("=DCOUNTA('database'!A:H,1," & criteria &
")")

Ideas?

Thank you.