0

I have

Dim tape, out As Worksheet
Set tape = ThisWorkbook.Sheets("Agg")
Set out = ThisWorkbook.Sheets("output")

out.Cells(1, 2).Value = WorksheetFunction.CountIfs(tape.Range("IG1:IG10000"), "<>" & "", "<>" & " ", "<>" & "  ")

So generally I am saying "count if it is not blank, not single space, not two spaces". What I'm trying to do is to count the non-blank values while taking into account there could be spaces in the cells. But this last line keep giving me this error: "Unable to get the Countifs property of the worksheetfunction class". How can I make it work? Thanks!

findwindow
  • 3,133
  • 1
  • 13
  • 30
Jenny Shu
  • 47
  • 2
  • 4
  • 12

2 Answers2

1

Maybe try something like 

out.Cells(1, 2).Value = tape.Evaluate("=SUM(IF(TRIM(IG1:IG10000)<>"""",1,0))")

That will ignore any cells which just have spaces.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
0

Countifs(..) works as a set of AND statements, not OR statments - e.g. if you had Surname/Forename/Gender, the code =COUNTIF(A:A,"Smith",C:C,"Female") would count Females in the Smith family. (You also have to specify each column separately)

The two solutions I can supply are A) have a macro to apply a TRIM to every cell in the intersect of UsedRange and Columnd IG to that all the spaces at the start/end vanish (and then possible clear the cell if LEN is 0?) or B) Use COUNTA to get non-blank and then subtract the other conditions:

Dim tape AS Worksheet, out As Worksheet, rTEMP as Range
Set tape = ThisWorkbook.Sheets("Agg")
Set out = ThisWorkbook.Sheets("output")

Set rTEMP = tape.Range("IG1:IG10000") 'Any specific reason for not just using the whole column IG:IG?

out.Cells(1, 2).Value = WorksheetFunction.CountA(rTEMP) - (WorksheetFunction.CountIfs(rTEMP,"<>",rTEMP,"""")+WorksheetFunction.CountIf(rTEMP," ")+WorksheetFunction.CountIf(rTEMP,"  ")) 'count of Non-blank cells minus the sum of counts for Non-blank 0-length, non-blank 1-space and non-blank 2-spaces

Set rTEMP = Nothing

(Checking for a non-blank 0-length cell is an extra special case, hence it uses CountIf)

Chronocidal
  • 51
  • 2