Countif, range, and cells

Question

I have the following code in VBA that is returning an 1004 error:

e = WorksheetFunction.CountIf(Worksheets("TG Teams").Range(Cells(4, w), Cells(12, w)), Worksheets("Calcs").Range("B" & a))

e, w, and a are all integers.

The code is meant to check if a name in the Calcs sheet also appears in a TG Teams sheet. The reason that the range is variable is that the CountIf needs to check a different column each week.

Any ideas how to resolve the error 1004 I get when I run this?

You need to assign the parent to **ALL** the range objects : `Worksheets("TG Teams").Range(Worksheets("TG Teams").Cells(4, w), Worksheets("TG Teams").Cells(12, w))` — Scott Craner, Apr 06 '17 at 14:22

score 3 · Accepted Answer · answered Apr 06 '17 at 14:23

3

You need to fully qualify (Cells(4, w), Cells(12, w)) as well with the worksheet ("TG Teams") they are in, use With:

With Worksheets("TG Teams")
    e = WorksheetFunction.CountIf(.Range(.Cells(4, w), .Cells(12, w)), Worksheets("Calcs").Range("B" & a))
End With

answered Apr 06 '17 at 14:23

Shai Rado

33,032
6
29
51

Thanks, this works perfectly. I'll accept it in a few minutes – Clauric Apr 06 '17 at 14:26
1

Why do I feel like "you need to fully qualify `Cells` calls" is the answer to 80% of VBA runtime error questions? – Mathieu Guindon Apr 06 '17 at 14:49

Countif, range, and cells

1 Answers1