0

I'm writing a countifs formula in VBA, and I would like to do the following code in one line:

Worksheets("Active_Users_Master").Range("I3").Value = [COUNTIFS(Active_Users_Master!A:A,"9",Active_Users_Master!D:D,Index(Clients,1))]
Worksheets("Active_Users_Master").Range("I3").Value = Worksheets("Active_Users_Master").Range("I3").Value + [COUNTIFS(Active_Users_Master!A:A,"9",Active_Users_Master!D:D,Index(Clients,2))]
Worksheets("Active_Users_Master").Range("I3").Value = Worksheets("Active_Users_Master").Range("I3").Value + [COUNTIFS(Active_Users_Master!A:A,"9",Active_Users_Master!D:D,Index(Clients,3))]
Worksheets("Active_Users_Master").Range("I3").Value = Worksheets("Active_Users_Master").Range("I3").Value + [COUNTIFS(Active_Users_Master!A:A,"9",Active_Users_Master!D:D,Index(Clients,4))]

If there is a way to have something such as a [Index(Clients,1:4)] or [Index(Clients,A:A)] that will take all values of the named range and add all the possibilities together?

I'm open to other suggestions as well. Ultimately, I am getting sources from many different areas that have different names for the same variable. I thought it would be best to make a named range and refer to that, but if anyone has better ideas, I'm open to it.

Thanks in advance!

wra
  • 237
  • 4
  • 7
  • 18

1 Answers1

0

You can use this line to index the 2nd row and first column in the clients named range

Range("Clients")(2, 1).Value
Ahmed AbdelKhalek
  • 184
  • 1
  • 11