Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNT A YES STATEMENT AGAINST A NAME | Excel Discussion (Misc queries) | |||
Count if statement | Excel Worksheet Functions | |||
If & Count Statement | Excel Worksheet Functions | |||
A week of dates and counting them using the COUNTIF function with contingencies | Excel Worksheet Functions | |||
life contingencies | Excel Worksheet Functions |