PDA

View Full Version : Activating multiple sheets with countifs function



xboon_95
08-04-2014, 12:39 AM
Hi all,

I am trying to use a countifs function to get my data on multiple sheets. The code is as follows :

ActiveSheet.Range("S1").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""P"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S2").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""I"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S3").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""O"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S4").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""C"",ActiveSheet!I:I,""1"")")
ActiveSheet.Range("S5").Value = Evaluate("COUNTIFS(ActiveSheet!D:D,""L"",ActiveSheet!I:I,""1"")")


As the sheet name varies, I'm not able to use the constant name Sheet1!. I tried changing (Sheet1!D:D,""P"",Sheet1!I:I,""1"") to (ActiveSheet!D:D,""P"",ActiveSheet!I:I,""1"") but it doesn't seem to work. Any idea how to do it? Any help will be appreciated. Thank you.

Bob Phillips
08-04-2014, 03:35 AM
Maybe this will work


With ActiveSheet

.Range("S1").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""P"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S2").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""I"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S3").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""O"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S4").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""C"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S5").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""L"",'" & .Name & "'!D:D,""!I:I,""1"")")
End With

ranman256
08-04-2014, 07:04 AM
You dont need the word ACTIVESHEET. Excel assumes your formula is for the activesheet so you dont need to put it in the formula. Just use the range.

xboon_95
08-05-2014, 02:59 AM
Maybe this will work


With ActiveSheet

.Range("S1").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""P"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S2").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""I"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S3").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""O"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S4").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""C"",'" & .Name & "'!D:D,""!I:I,""1"")")
.Range("S5").Value = Evaluate("COUNTIFS('" & .Name & "'!D:D,""!D:D,""L"",'" & .Name & "'!D:D,""!I:I,""1"")")
End With

Thank you very much (:

Bob Phillips
08-05-2014, 05:22 AM
You dont need the word ACTIVESHEET. Excel assumes your formula is for the activesheet so you dont need to put it in the formula. Just use the range.

It is true that you don't, but it is not a good practice to omit a sheet reference, including it shows that you have explicitly decided you want it to work on the activesheet, and it is much easier to change code that is explicit if things change.