Count Cells in VBA

[irish634]

Here's something that has been giving me fits.

In my work book, if I use the following in a cell, I get a count of all cells in the range that are populated:

Count(C22,F22,I22,L22,O22)

I am trying to do this in vba:

Public Function No_Conditions(M_Range As Range)

    No_Conditions = WorksheetFunction.count(M_Range)

End Function

With the same selection, the VBA code returns "#Value!"

I assume it's not liking my range selection?


If I do this the code works:

Public Function No_Conditions(Cond1, Cond2, Cond3, Cond4, Cond5)
    
    No_Conditions = WorksheetFunction.count(Cond1, Cond2, Cond3, 

End Function

If I want to be able to select more than a specified number of cells, how would I code that?

 

[Brianwarnock]

I think that you are going to have to do somethink like

Public Function No_Conditions()
Dim myrange As Range
Set myrange = ActiveWindow.RangeSelection
    No_Conditions = WorksheetFunction.Count(myrange)
ActiveSheet.Range("j1").Value = No_Conditions 'cant figure how to avoid hardcoding this cell.

End Function

And then put a command button on your worksheet to call a sub that runs the function

Private Sub CommandButton1_Click()
Run No_Conditions()
End Sub

You can then highlight all the cells and ranges, hit the command button and get the result.

Brian

 

[chergh]

You could perhaps use the union function.

Public Function No_Conditions(M_Range As Range) as long

dim bigRng as range
dim rng as range

for each rng in M_Range

if bigRng is nothing then

   set bigrng = rng

else

   set bigRng = union(bigRng, rng)

end if

next rng
    

No_Conditions = worksheetfunction.count(bigRng)
End Function

Not sure if this will work as I don't have excel at home.

 

[Brianwarnock]

It gives the same #Value error as the first if more than one parameter is passed, surely to do what irish wants, or atleast what I presume that he wants namely to pass non contiguous ranges, you would need to say pass a string or array and parse it into the many ranges, and then build the Count(r1,r2,....) or maybe sum multiple Counts.

Chergh I know that your expertise is better than mine and hope that you can comment or show me where I am wrong, if I am.

Brian

 

[chergh]

Might as well just count it manually:

Public Function No_Conditions(M_Range As Range) as long

dim rng as range
dim lCount as long

lCount = 0

for each rng in m_range

if isnumeric(rng.value) then

      lCount = lCount + 1

end if

next rng


    No_Conditions = lCount

End Function

 

[Brianwarnock]

I take that back it works, but you have to use double (())

ie
=NO_CONDITIONS((B2:B7,D3,D12))

Brian

 

[Brianwarnock]

Haven't tested your last one, which do you recommend?

Brian

 

[Brianwarnock]

Oh dear , the very first Function by Irish works with (( )).

Well that was educational, but why didn't I try double brackets last night.

Brian

 

[chergh]

After you posted about the double brackets I tried it as well with all the functions and it seems it is always required. Not something I've come across before.

My last function is a pile of crap apparently as it seems to return true to emptycells being numeric.

The op would be best to forget all the above functions and just use the count function with double brackets.