Convert CountIfs formula to VBA

[welchs101]

I have the following excel worksheet formula:

=COUNTIFS(Inputfile_InQQR!$B$2:$B$5123,"=" & Summary_InQQR!A2,Inputfile_InQQR!$W$2:$W$5123,"Yes",Inputfile_InQQR!$F$2:$F$5123,"Random",Inputfile_InQQR!$AA$2:$AA$5123,"Data Error",Inputfile_InQQR!$C$2:$C$5123,IF((Summary_InQQR!C2="Overall"),"<>" & """",IF((Summary_InQQR!C2)="ABC","ABC","XYZ")))

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?

[Kyle123]

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

[welchs101]

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.........

[welchs101]

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

its in a for loop

If sht_array3(x, 1) = "Overall" Then


    unit_de_array(x, 1) = (Application.WorksheetFunction.CountIfs( _
        date_range, "=" & date_array1(x, 1), _
        Error_YN_Range, "=" & "Yes", _
        TypeReview_Range, "=" & "Random", _
        Errorcat_Range, "=" & "Data Error", _
        Site_Range, "<>" & ""))
        

ElseIf sht_array3(x, 1) = "ABC" Then

    unit_de_array(x, 1) = (Application.WorksheetFunction.CountIfs( _
        date_range, "=" & date_array1(x, 1), _
        Error_YN_Range, "=" & "Yes", _
        TypeReview_Range, "=" & "Random", _
        Errorcat_Range, "=" & "Data Error", _
        Site_Range, "=" & "ABC"))
        

ElseIf sht_array3(x, 1) = "XYZ" Then

    unit_de_array(x, 1) = (Application.WorksheetFunction.CountIfs( _
        date_range, "=" & date_array1(x, 1), _
        Error_YN_Range, "=" & "Yes", _
        TypeReview_Range, "=" & "Random", _
        Errorcat_Range, "=" & "Data Error", _
        Site_Range, "=" & "XYZ"))
        

End If

[Kyle123]

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

[welchs101]

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.

[romperstomper]

Why not just put the formulas in the cells then convert them to values immediately?

[welchs101]

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.

[romperstomper]

For example:

    With Range("A1:A1000")
        .FillDown
        .Value2 = .Value2
    End With

Note: if you have calculation set to manual, you'll need to calculate the range first, or use the Formula property to assign the formula to the cells, and then convert to values.

[romperstomper]

PS you can also use Evaluate with your original formula string and assign the result to the range directly if you prefer.

[welchs101]

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

[romperstomper]

Yes, it is.
(well, strictly speaking, I would probably redesign the entire workbook if it were that slow)

[welchs101]

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

[welchs101]

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

[romperstomper]

That's not really redesigning, just working around the issue.
Did you try the previous code?

[welchs101]

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

[romperstomper]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save