1

I am using this formula in Excel VBA to insert the CountIFS() formula, however the count returned is always 0?

This is the VBA syntax I am using: (I formatted it to add a space after the comma to look better in this window, in my VBA it is all one line)

With ws
.Range("C2:C" & lr).FormulaR1C1 = "=COUNTIFS(Students!C[-2],RC[-2],
                              Students!C,""<>James"",
                              Students!C,""<>Joanne"",
                              Students!C,""<>Lisa"",
                              Students!C,""<>Marin"",
                              Students!C,""<>Alfie"",
                              Students!C,""<>Parent Volunteer"",
                              Students!C[6],""'Current"")                             
End with

Which if I look at the formula after the VBA inputs it into the cell it eqates to this:

=COUNTIFS([Students.xlsx]Names!A:A,A2,
[Students.xlsx]Names!C:C,"<>James",
[Students.xlsx]Names!C:C,"<>Joanne",
[Students.xlsx]Names!C:C,"<>Lisa",
[Students.xlsx]Names!C:C,"<>Marin",
[Students.xlsx]Names!C:C,"<>Alfie",
[Students.xlsx]Names!C:C,"<>Parent Volunteer",
[Students.xlsx]Names!I:I,"Current")

Which that looks like the correct syntax. What do I need to change in this syntax in order to have it provide accurate counts?

IcyPopTarts
  • 494
  • 1
  • 12
  • 25
  • Looks and tests correct on a single worksheet. Check for leading/trailing spaces, non-breaking spaces or unprintable characters in Names' columns A and I. –  Mar 18 '17 at 22:26
  • There are no leading/trailing spaces. What would be considered an unprintable character? – IcyPopTarts Mar 18 '17 at 22:34
  • 1
    Try filtering column A = A2, column I = "Current" and verify that there is at least 1 name in column C that isn't an excluded name. – Mark Fitzgerald Mar 18 '17 at 22:41
  • @MarkFitzgerald - yes, I just verified that condition is met. Meaning >= 1 name is returned when fitlered. – IcyPopTarts Mar 18 '17 at 22:45
  • There are many but a common one would be a CHAR(10) or CHAR(13) left over from a copy and paste off a web page. The `=LEN(A2)` will show an extra character in length; **Current** should be 7 characters long. –  Mar 18 '17 at 22:46
  • 1
    @Jeeped - that was it, there was CHAR(13) in the cells! – IcyPopTarts Mar 18 '17 at 22:53

0 Answers0