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

HTH

"markose" wrote:


Hi, I am trying to assign two different cell addresses to two different
variables based on several differnt contingencies. I then want to pass
these variables in a formula (count formula). In the code below I want
to pass countbegin and countend as arguments in the count formula. I
want countbegin and countend to be the addresses of specific cells
based on some criteria. Here is the code I have been trying to work
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!


--
markose
------------------------------------------------------------------------
markose's Profile: http://www.excelforum.com/member.php...o&userid=31501
View this thread: http://www.excelforum.com/showthread...hreadid=511762