VBA Error - Count Number of Asterisks in Array Column - Object Error

[khabi21]

Hello,
I have an array that can have a different number of columns and rows. Sometimes there can be *'s as the values. I am trying to eliminate them, but first I need to be able to count the number of *'s in each column. I have to step through each column at a time because of other steps I do immediately after. I am trying the code below, but it is giving me an "Object Required" error at the AstCount line.

For J = LBound(BS_Array, 2) To UBound(BS_Array, 2)
AstCount = WorksheetFunction.CountIf(Application.Index(BS_Array, , J), "~*")
Next J

The BS_Array array does exist, it works fine in every other part of the code. I have also dimmed AstCount as an Integer.

I also tried using just "*" as the argument in the countif statement, but that didn't work either, and I read somewhere online that a ~ needs to be placed before the asterisk actually look for an asterisk.

Any help would be appreciated.

 

[Momentman]

Whats the declaration for BS_Array? and what is set to? Can you post that portion of the code?

 

[AlphaFrog]

The Countif function doesn't accept an array. It requires a range.

You could loop through the array and keep a running count.

[color=darkblue]For[/color] i = 1 [color=darkblue]To[/color] [color=darkblue]UBound[/color](BS_Array, 1)
    [color=darkblue]If[/color] BS_Array(i, j) = "*" [color=darkblue]Then[/color] AstCount = AstCount + 1
[color=darkblue]Next[/color] i

 

[khabi21]

Whats the declaration for BS_Array? and what is set to? Can you post that portion of the code?

Code is below. There are no issues with the array. For this instance, it ends up being a 17 by 18 array.

Dim BS_Array() As Variant
        BS_FirstRow = Application.Match("Back Spin", .Range("A:A")) + 1
        BS_FirstCol = 2
        BS_FirstCell = .Cells(BS_FirstRow, BS_FirstCol).Address
        BS_LastCol = Range(BS_FirstCell).End(xlToRight).Column
        BS_LastRow = Range(BS_FirstCell).End(xlDown).Row
        BS_LastCell = .Cells(BS_LastRow, BS_LastCol).Address
        BS_Rows = Range(Range(BS_FirstCell), (BS_LastCell)).Rows.Count
        BS_Columns = Range(Range(BS_FirstCell), (BS_LastCell)).Columns.Count
    
        ReDim BS_Array(1 To BS_Rows, 1 To BS_Columns)
        BS_Array() = Range(Range(BS_FirstCell), Range(BS_LastCell))

 

[AlphaFrog]

This could work as well, though it's probably not as efficient as looping through the array in post #3.

[color=darkblue]Dim[/color] MyRng [color=darkblue]As[/color] Range
[color=darkblue]Set[/color] MyRng = Range(Range(BS_FirstCell), Range(BS_LastCell))


AstCount = Application.CountIf(MyRng.Columns(j), "~*")

 

[khabi21]

The Countif function doesn't accept an array. It requires a range.

You could loop through the array and keep a running count.

[COLOR=darkblue]For[/COLOR] i = 1 [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](BS_Array, 1)
    [COLOR=darkblue]If[/COLOR] BS_Array(i, j) = "*" [COLOR=darkblue]Then[/COLOR] AstCount = AstCount + 1
[COLOR=darkblue]Next[/COLOR] i

I ended up adapting this and it is working. Thank you for the help!

 

[AlphaFrog]

You're welcome. Thanks for the feedback.