Count statement with several contingencies

[markose]

Hi, I am trying to assign two different cell addresses to two differen
variables based on several differnt contingencies. I then want to pas
these variables in a formula (count formula). In the code below I wan
to pass countbegin and countend as arguments in the count formula.
want countbegin and countend to be the addresses of specific cell
based on some criteria. Here is the code I have been trying to wor
with:

Range("b2").Select

If ActiveCell.Offset(0, -1) = "CONTROL" Then

'Range("ActiveCell.Select").Select
'Set Countbegin = Range("ActiveCell.Select")


Countbegin = Selection.Address


Do

If ActiveCell.Offset(1, -1) = "" Then


ActiveCell.Offset(1, 0).Select


ElseIf ActiveCell.Offset(1, -1) = "Begin #" Then


'Selection.End("ActiveCell.Select").Select


Countend = ActiveCell.Address


Exit Do


End If


Loop

ActiveCell.Offset(0, 15).Select

ActiveCell.Formula = "=count(Countbegin:Countend)"

End If




'ActiveCell.Formula = "=Count(Selection)"


'Count_answer = application.count(Countbegin:Countend)



End Sub



Thanks

 

[Tom Ogilvy]

ActiveCell.Formula = "=count(" & _
Countbegin & ":" & Countend & ")"

 

[Guest]

ActiveCell.Formula = "=count(" & Countbegin & ":" & Countend & ")"

HTH