MsgBox CountIf

[julhs]

Need some help to understand where I’m going wrong/not understanding the 3rd Count method in my code.
Background info;

The NamedRange is "MyNamedRangeA" and RefersTo A8:A28
Cells A8:A28 contain Array formula
Cells A8:A27 contain the values of the formula
Cell A28 has NO returned value, just the formula

My code to date

Sub MyTest_CountIF()
Dim rng1 As Range
Set rng1 = Range("MyNamedRangeA")
MsgBox WorksheetFunction.CountIf(Range("A8:A28"), "> """) , "") ' count returns "20"
MsgBox WorksheetFunction.CountIf(Range("MyNamedRangeA "), ">""") ' count returns "20"
MsgBox WorksheetFunction.CountIf(Range("rng1"), ">""") ' count returns "0" and NOT expected "20"
End Sub

As a supplementary observation:
MsgBox WorksheetFunction.CountBlank(rng1) ' count returns "1"
MsgBox WorksheetFunction.CountIf(Range("rng1") , "") ' count returns "1"
which I have to assume !!! is the # of Blanks

 

[6StringJazzer]

Cells A8:A28 contain Array formula
Cells A8:A27 contain the values of the formula
Cell A28 has NO returned value, just the formula

I can't make sense of this. What is in each of these cells? A formula has to have either a returned value or an error. How can A28 have no returned value, just a formula?

What are you trying to count?

MsgBox WorksheetFunction.CountIf(Range("A8:A28"), "> """) , "") ' count returns "20"

The syntax you are using in this and the subsequent two lines of code don't make sense. You are using the > operator, to determine if the values in the given range are greater than....something. But you are comparing them to a quote " mark. What do you intend to mean by checking if a cell is

>"

?

Also the syntax in your last line is wrong. rng1 is a variable, not a range name

MsgBox WorksheetFunction.CountIf(Range("rng1"), ">""") ' count returns "0" and NOT expected "20"

should be

MsgBox WorksheetFunction.CountIf(rng1, ">""") ' count returns "0" and NOT expected "20"

 

[julhs]

Thanks for the reply Jeff.
Here is the array formula that’s in the cells $A$8:$A$28 that is the named range "MyNamedRangeA"

{=IFERROR(INDEX($B$6:$B$112,SMALL(IF($B$6:$B$112<>"",ROW($B$6:$B$112)-ROW($B$6)+1,""),ROW(A8)-ROW($A$8)+1)),"")}

The formulas pull all the values from a non-contiguous list, $B$6:$B$112 (values are unevenly spaced) and creates a compressed contiguous one in "MyNamedRangeA" range.
The reason for the count is IF there are more values in ($B$6:$B$112) than there are cells with array formula in "MyNamedRangeA" the contiguous list will be incomplete,
so a new row/rows needs to be inserted into "MyNamedRangeA" and the array formula filled down.
If the reverse of that is true I will need to delete a row from "MyNamedRangeA", because I don’t want a cell with null value
My apologies, I messed up here with a copy and paste,

MsgBox WorksheetFunction.CountIf(Range("A8:A28"), "> """) , "") ' count returns "20"

should have read,

MsgBox WorksheetFunction.CountIf(Range("A8:A28"), ">""") ' count returns "20"

Essentially you did pick up my error with,

MsgBox WorksheetFunction.CountIf(Range("rng1"), ">""") ' count returns "0" and NOT expected "20"

needing to be,

MsgBox WorksheetFunction.CountIf (rng1, ">""")

I had run tests on so many different variations I somewhat lost sight of things.
And my supplementary observation didn’t do anything other than add to my confusion

MsgBox WorksheetFunction.CountIf(Range("rng1") , "") ' count returns "1" which I have to assume !!! is the # of Blanks

Many thanks for your assistance, very much appreciated and sorry for the confusion I created.
julhs