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?

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

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.

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

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

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

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

Brian

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

Brian

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

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

Brian

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.