Convert CountIfs formula to VBA

I have the following excel worksheet formula:

Please Login or Register  to view this content.

I am trying to convert this to a vba formula........my issue is with the "IF" statement that i have inside the countifs function.............not sure how to handle this.

I am using in vba the "Application.WorksheetFunction.CountIfs" to duplicate the formula. However, like i said i am not sure how to handle the "IF" statement inside the countifs.

any ideas?

Why not just keep the formula in Excel and get the value in your code?

Why do you want to convert it to VBA? VBA will be slower

its a long story but instead of using this formula in excel and then filling down a bunch of formulas i am doiong everything in arrays and then pasting the values stored in the arrays.........

This is all i could come up with...........

its in a for loop

Please Login or Register  to view this content.

I really think you should reconsider, whilst useful, worksheet functions in VBA are generally slow and to use this many of them, I'd need an exceptionally good reason - one I can't think of from the top of my head.

Can you tell us a bit more about what you're trying to do and we might be able to make some alternative suggestions

i have this big excel file........and its taking a long time to run.......i found (through this site asking questions) that the cause of my program taking a long time to run is the calculations/formulas autocalculating...........so i am removing those formulas in my file that are not needed and in their place i am using arrays to calculate the values. I am then putting the values in the cells instead of the formulas.

so i put in the formulas......then i do a fill down.........then i would have to convert to values........

how would you do this? i have my ideas but i thought it would take longer that way.

is this the way you would do it instead of using the application.worksheetfunctions?

thats kinda what i am trying to do via not using formulas where i can but instead values........

so how wouldi use the Evaluate.........

yes, with the .value inside the with statement.......worked great.........just wish i had known about this earlier..................