PDA

View Full Version : Solved: Using Programmed Array in Worksheet Function



asingh
10-15-2008, 10:49 AM
Hi,

I am trying to use a VBA generated array in a standard worksheet function via VBA code.

To test this:

Sub tst()
Dim arr_all_spcs()

Dim k As Long

arr_all_spcs = Range("rng_BIO_tbl_rstrct")

'this works
k = Application.WorksheetFunction.Count(arr_all_spcs)

'this does not work
'k = Application.WorksheetFunction.CountIf(arr_all_spcs,"A")

End Sub
Obviously in the above mentioned exampe I have forcefully loaded my array
with the named range contents from rng_BIO_tbl_rstrct. But in my program I will be loading the array with values, after detailed procedure and function calls.

I get the error: "type mistmatch"

There is a workaround..I have thought...is to push the arr_all_spcs array onto a temporary worksheet, and then do the countif...but if I could do it all through the VBA it would be awesome..! OR I can sequentially cycle through all the array elements using a simple FOR loop...compare each element to my "k"...again...to much typing...! :)


thanks a lot for the help,

asingh

Kenneth Hobs
10-15-2008, 10:58 AM
Make your arrays Variant and 2 dimensional. If just 1 dimension, use WorkSheetFunction.Transpose.

asingh
10-15-2008, 11:02 AM
The watch window shows it as:

Watch : - : arr_all_spcs : : Variant/Variant(1 to 19, 1 to 6) : mod_Public_declarations.tst

Kenneth Hobs
10-15-2008, 11:16 AM
Post example data for the range or a sample workbook.

asingh
10-15-2008, 11:27 AM
Hi,

Attached is a sample book with the code. Module 1 has the code.

Or is there any way..I can transpose/convert the array to a standard excel range, but without having to load data onto a sheet..??

regards,

asingh

Kenneth Hobs
10-15-2008, 11:52 AM
Since you are only counting cells with numbers, I am not sure why you used "a".

The countif in this example uses Evaluate and your named range to count the number of 0 entries.
Sub tst()
Dim arr_all_spcs As Variant
Dim k As Long
Set arr_all_spcs = Range("rng_BIO_tbl_rstrct") '//load the array with all metrics
k = Application.WorksheetFunction.Count(arr_all_spcs, "a")
Debug.Print "Count", k
k = Evaluate("CountIf(" & "rng_BIO_tbl_rstrct" & ", " & "" & "0" & ")")
Debug.Print "CountIf", k
End Sub

Paul_Hossler
10-15-2008, 12:55 PM
1. How do you want to use the array after it's filled?

2. CountIf() works if you give it a Range and not an Array


k = Application.WorksheetFunction.CountIf(Range("rng_BIO_tbl_rstrct"), "auto")


Also need to CountIf on "Auto" if that's the value you're looking for, since there are no "a" in the range

Paul

asingh
10-15-2008, 06:59 PM
Hi,

I would be checking if a certain element..exists more than once in my dynamically generated array.

I will only use the array..for verification purposes as mentioned above.

I will not be looking at numbers here, they would be strings only.

Sorry if all was not clear in my previous posts.

Krishna Kumar
10-16-2008, 06:25 AM
I think countif won't work in arrays.

Paul_Hossler
10-16-2008, 08:24 AM
just pass your range as a Range to CountIf



k = Application.WorksheetFunction.CountIf(Range("rng_BIO_tbl_rstrct"), "auto")


and see if your value is in there more than once

Paul

asingh
10-16-2008, 08:48 AM
Actually I had used "rng_BIO_tbl_rstrct", just for test purposes...even I think...countif will not take a programmed array.. :(

Paul_Hossler
10-16-2008, 06:59 PM
CountIf only works with ranges, so I guess your original idea about running the array into a range on a worksheet is the only want to use CountIf

Of course, you could always For / Next your way through the array counting as you went.


Option Explicit
Sub Counting()
Dim i As Long
Dim A(1 To 10, 1 To 2) As Variant
Dim R As Range


For i = 1 To 6
A(i, 1) = "A"
A(i, 2) = "B"
Next i

For i = 7 To 10
A(i, 1) = "C"
A(i, 2) = "D"
Next i

Application.ScreenUpdating = False

On Error Resume Next
Application.DisplayAlerts = False
Worksheets("scratch").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Worksheets.Add.Name = "scratch"
Set R = Worksheets("scratch").Cells(1, 1).Resize(UBound(A, 1), UBound(A, 2))
R.Value = A

MsgBox Application.WorksheetFunction.CountIf(R, "A")
MsgBox Application.WorksheetFunction.CountIf(R, "B")
MsgBox Application.WorksheetFunction.CountIf(R, "C")
MsgBox Application.WorksheetFunction.CountIf(R, "D")


Application.DisplayAlerts = False
Worksheets("scratch").Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub



Paul

asingh
10-17-2008, 11:22 PM
Did something to that....and evaluated..

thanks to all..