COUNTIF() Counting "" 1

[grnzbra]

The range EquipmentID is, in this screen shot, F13:F18.

The contents of the cells in the A column is set by

Range("A" & Target.Rows(i).row) = "=COUNTIF(EquipmentID,F" & Target.Rows(i).row & ")"
There's a loop with an IF statement to see if B(i) is empty, if it is, it adds the COUNTIF() to A(i) and the hyperlink to B(i)​

If a duplicate is entered into a cell in the EquipmentID range, the code sees it and sets the offending cell to ""
The result is that the COUNTIF() in the corresponding A cell will show the sum of the one "" just entered and the ""
in the last cell of EquipmentID (F18)

In this case, I just highlighted the F16 cell and hit DELETE and it was still counted.

My understanding was that COUNTIF() isn't supposed to count spaces (or is it NULLs)

This is a spreadsheet that I was given to modify. I added the code to insert the COUNTIF() in the A column cells.

When I start with a new workbook and manually construct the table, the COUNTIF() statements work fine - they don't count the empty cells.

By the way, I did try Range("A" & Target.Rows(i).row) = "=COUNTIF(EquipmentID,F" & Target.Rows(i).row & ") - COUNTIF(EquipmentID,"""")"
In that case, the first COUNTIF() statement did not count the blank cells while the second one did, resulting in negative numbers.

Is there some kind of setting or statement that I should look for that could be telling the COUNTIF() function to count empty cells?

 

[SkipVought]

Select F17, the "empty" cell and hit DELETE.

="" is not EMPTY!

Instead of ...

If a duplicate is entered into a cell in the EquipmentID range, the code sees it and sets the offending cell to ""

...use .ClearContents

BTW, VBA questions are best addressed in forum707.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[grnzbra]

Ta-DAAAH! That did the trick. Thank you very much. I had the feeling that "" wasn't EMPTY (actually I think in terms of NULL) but couldn't figure an alternative.

 

[SkipVought]

BTW, instead of VBA, you could use a Data/Validation custom formula to prevent a duplicate from being entered in the first place by using COUNTIF($F$13:F13,F13) in that range.

I'd also make the table a Structured Table. ALL formulas will propagate though all table rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]