CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
joehua
joehua

Reputation: 735

CountA returns 0, WorksheetFunction.CountA returns 1

In the debug window, the following expressions all return one.

Application.WorksheetFunction.CountA(Cells(4 + (i - 1) * rows_per_record, 28) & ":" & Cells(5 + (i - 1) * rows_per_record, 58))

Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(n).Cells(4 + (i - 1) *  rows_per_record, 28) & ":" & ThisWorkbook.Sheets(n).Cells(5 + (i - 1) * rows_per_record, 58))

In this particular case, the range is AB60:BF61, which is confirmed in debug window.

In the worksheet, while the script is paused, I enter in a cell =CountA(AB60:BF61) and the result is 0, which is what it should be.

Any explanation will be greatly appreciated.

Windows 7, Excel 2010

Upvotes: 4

Views: 2729

Answers (2)

user4039065
user4039065

Reputation:

I believe you have the answer and one version of the correct syntax in another response. Here is an alternate:

dim c as long
with ThisWorkbook.Sheets(n)
    c = Application.CountA(.range(.Cells(4 + (i - 1) * rows_per_record, 28), _
                                  .Cells(5 + (i - 1) * rows_per_record, 58)))
    debug.print c
end with

The Range object is constructed by supplying the upper-left and lower right corners with two Range.Cells properties. The parent worksheet is referenced through the With ... End With statement.

Upvotes: 1

user6432984
user6432984

Reputation:

Your syntax is wrong.

Correct Syntax:

Application.WorksheetFunction.CountA (Range(Cells(4 + (i - 1) * rows_per_record, 28), Cells(5 + (i - 1) * rows_per_record, 58)))

Your code is counting each argument that is not an empty string. The only argument is a colon surrounded by the two empty cell values.

Application.WorksheetFunction.CountA(ThisWorkbook.Sheets(n).Cells(4 + (i - 1) * rows_per_record, 28) & ":" & ThisWorkbook.Sheets(n).Cells(5 + (i - 1) * rows_per_record, 58))

enter image description here

Upvotes: 2

Related Questions