COUNTA reports a value of 1 when there's nothing to count

[BlackieHamel]

I have a macro that reports how many rows have data in Column A of a subsidiary worksheet. The code is

ActiveCell.FormulaR1C1 = "=counta(indirect(RC[-1] & ""!A:A""))"

Thus if the cell to the left says "BUY" I get the number of rows with data in worksheet BUY.

My problem is that if BUY does not exist, it reports 1, not 0. Some worksheets do have just one row in them, so this leads to confusion: is there just one, or does the sheet not exist?

How can I adjust this to report that the sheet does not exist?

Thanks.

Blackie

 

[vds1]

Check for spaces in column A in BUY Sheet. Even a small space may reports as non blank cell.

 

[BlackieHamel]

Thanks, but that's not the problem. COUNTA is reporting there is 1 entry in a worksheet that does not exist.

 

[JoeMo]

COUNTA counts the total number of cells that are not truly blank in the range that is its argument. If you have a formula in that range that is returning "" COUNTA will count that as a non-empty cell.

 

[BlackieHamel]

Joe,

Does this mean there's no solution to my problem? Is there no way to distinguish between a worksheet that has a value in one row and a non-existent worksheet?

Blackie

 

[JoeMo]

Joe,

Does this mean there's no solution to my problem? Is there no way to distinguish between a worksheet that has a value in one row and a non-existent worksheet?

Blackie

If you want to know if the sheet named BUY exists in the workbook your code is in you can use;

If SheetExists("BUY") then
'do something
Else
'do something else
End If
'Rest of code
End Sub
Function SheetExists(shName As String) As Boolean
SheetExists = False
For Each sh In ThisWorkbook.Sheets
    If sh.Name = shName Then
        SheetExists = True
        Exit For
    End If
 Next sh
End Function

 

[BlackieHamel]

Thanks, Joe -- that did the trick!
Blackie