How to count negative values in a table with VBA

[Romano_odK]

Good morning,

Got table and I would like to count the how many lines there are where the value in column J is negative. Table starts at line 6 and is called "Locatie". Can this be done?

Thank you for your time.

 

[Peter_SSs]

What is the table 'Locatie' heading in column J?

 

[bebo021999]

How about

Option Explicit
Sub countNeg()
Dim myCol As Range
Set myCol = Application.Intersect(Range("locatie"), Columns("J")) ' column J in that table
MsgBox WorksheetFunction.CountIf(myCol, "<0")
End Sub

 

[Romano_odK]

What is the table 'Locatie' heading in column J?

That would be "Voorraad".

 

[Romano_odK]

How about

Option Explicit
Sub countNeg()
Dim myCol As Range
Set myCol = Application.Intersect(Range("locatie"), Columns("J")) ' column J in that table
MsgBox WorksheetFunction.CountIf(myCol, "<0")
End Sub

View attachment 100447

Could you instead of reporting it in a message box place the result in cell J1 for example?

 

[Peter_SSs]

If it is a formal table and you want the result in J1, why not just use a simple formula in that cell?

Romano_odK.xlsm
	J
1	3
2
3
4
5
6	Voorraad
7
8	1
9	5
10	-3
11
12	0
13	-2.3
14	-3
15	4
16
17
Sheet1

Cell Formulas
Range	Formula
J1	J1	=COUNTIF(Locatie[Voorraad],"<0")

 

[bebo021999]

Could you instead of reporting it in a message box place the result in cell J1 for example?

Should be:

=Range("J1").value =  WorksheetFunction.CountIf(myCol, "<0")

 

[Peter_SSs]

If it really needs to be vba then this one-liner should do it.

Sub CountLessThanZero()
  Range("J1").Value = Application.CountIf(Range("Locatie[Voorraad]"), "<0")
End Sub

 

[Romano_odK]

If it is a formal table and you want the result in J1, why not just use a simple formula in that cell?

Romano_odK.xlsm
	J
1	3
2
3
4
5
6	Voorraad
7
8	1
9	5
10	-3
11
12	0
13	-2.3
14	-3
15	4
16
17
Sheet1

Cell Formulas
Range	Formula
J1	J1	=COUNTIF(Locatie[Voorraad],"<0")

That is a good remark, I will eventually use this to report in a command button. So this is step 1. Thank you for your reply and have a great day.

 

[Romano_odK]

Should be:

=Range("J1").value =  WorksheetFunction.CountIf(myCol, "<0")

Thank you this is what I needed, have a great day.