Count statement with several contingencies
 

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

--
markos
-----------------------------------------------------------------------
markose's Profile: http://www.excelforum.com/member.php...fo&userid=3150
View this thread: http://www.excelforum.com/showthread.php?threadid=51176

Count statement with several contingencies
 

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

--
Regards,
Tom Ogilvy


"markose" wrote in
message ...

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

Count statement with several contingencies
 

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