I am counting the rows in a selected column using the following code which is
working fine. However, I would like to modify this to count the rows where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

Sub RowCount()
Dim x, Xcount
For Each x In Selection
If x.Value < 0 Then Xcount = Xcount + 1
Next
MsgBox ("Numbers of rows < 0 : ") & Xcount
End Sub

might be easier to just use

Sub countifnotzero()
MsgBox Application.CountIf(Columns(5), "<0")
'or
'MsgBox Application.CountIf(Selection, "<0")
End Sub
--
Don Guillett
SalesAid Software

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<0")

I've tried modifying the suggested code by adding the 0 but have had no
success.



"Bob Phillips" wrote:

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<0")

I've tried modifying the suggested code by adding the 0 but have had no
success.



"Bob Phillips" wrote:

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code
which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

That's it

"Bob Phillips" wrote:

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<0")

I've tried modifying the suggested code by adding the 0 but have had no
success.



"Bob Phillips" wrote:

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code
which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

I need to modify this formula depending on the value in the cell immediately
above the active cell as follows:

if value < 0 then "=COUNTIF(" & rng.Address & ",""<0"")-1"
else
if value = 0 then "=COUNTIF(" & rng.Address & ",""<0"")"

I am having problems with the syntax in referring to another cell.

Thanks

"Bob Phillips" wrote:

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<0")

I've tried modifying the suggested code by adding the 0 but have had no
success.



"Bob Phillips" wrote:

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following code
which
is
working fine. However, I would like to modify this to count the rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<0"")" & _
IIf(rng1.Offset(-1, 0).Value < 0, "-1", "")


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I need to modify this formula depending on the value in the cell
immediately
above the active cell as follows:

if value < 0 then "=COUNTIF(" & rng.Address & ",""<0"")-1"
else
if value = 0 then "=COUNTIF(" & rng.Address & ",""<0"")"

I am having problems with the syntax in referring to another cell.

Thanks

"Bob Phillips" wrote:

Isn't it as simple as

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<0"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
Bob,

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"

returns the following cell formula:

=COUNTIF($T$1:$T$2150,"<")

however the cell formula I need is:

=COUNTIF($T$1:$T$2150,"<0")

I've tried modifying the suggested code by adding the 0 but have had
no
success.



"Bob Phillips" wrote:

rng1.Formula = "=COUNTIF(" & rng.Address & ",""<"")"


--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Brian C" wrote in message
...
I am counting the rows in a selected column using the following
code
which
is
working fine. However, I would like to modify this to count the
rows
where
the value < 0.

Please help

Dim rng As Range, rng1 As Range
Set rng = Selection
If rng.Areas.Count 1 Then
MsgBox "non contiguous areas selected - exiting"
Exit Sub
End If
If rng.Columns.Count 1 Then
MsgBox "multiple columns selected = exiting"
Exit Sub
End If
Set rng1 = rng(rng.Count).Offset(1, 0)
rng1.Formula = "=Count(" & rng.Address(1, 1) & ")"