COUNTIFS in VBA Macro?

[cluelessdood]

Hi, everyone. I'm trying to use the following in a VBA macro, but it just doesn't want to work for me. I'm getting "Expected: End of Statement." I understand it's because of the quotation marks, but it's the only way I thought I could pull in ">0." Basically, I'm trying

Here was the original line of code:

TempString = "IF(RC[4]>0,""1,"","""")

Here is the code I'm trying to use. Basically, I want it to use two conditions, rather than one now:

TempString = "IF(COUNTIFS(R[-1]C[4],"*MAP*", RC[4], ">0"),""1,"","""")"

[davesexcel]

First thing I see is you need to start the formula with an "=" sign is should be "=IF(COUNTIFS(R[-1]C[4],"*MAP*", RC[4], ">0"),""1,"","""")"

[cluelessdood]

Thank you, davesexcel. I have a question about performance. I'm trying to count cell values ONLY when the column name is like Math, Science, or History. The issue is that the code is somewhat static. If a Math, History, or Science column is deleted, it throws off the counts and ends up counting the next column that should be in the spot of one of the grades. I want columns to only be counted when the column name is Math, Sci, or History.

Before macro
Attachment 717578

After macro
Attachment 717579

After macro (when a column was deleted beforehand)
Attachment 717580

My updates are slowing down performance considerably (from a minute to 5+ minutes). Is there anything I can do? Columns("G:G") is the updated code. Code("H:H") and ("I:I") are the original code that I had.

Please Login or Register  to view this content.

[cluelessdood]

I've attached sample file.