more efficient way to do for-loop using application.worksheetfunction.countifs

Hi,

I have a macro which takes ~ 46sec to run.

In this file:
Two sheets of data: Out_Sht, Query_sht

I use a for-loop going through each of the LotID values on the Out_Sht using an application.worksheetfunction.countifs designation to count using specified criteria (see code).

What i have works but i takes a really long time and i was wondering if there was a better way more efficient way.

Any suggestions?

dont want to influence anyone's suggestions but i was thinking a "scripting dic" approach might work but not sure how to do it.........i am kinda learning more and more about scripting dic-stuff but not sure how to handle that very complex countif and still use script dic.

why more rows in output........long story too long to explain..........it suffices to say that the output worksheet comes from another file and i have to create extra cols of data for this file along specified guidelines...........

what am i calculating:
I am calculating two things (see enclosed code in file):

First:

Please Login or Register  to view this content.

calculating value and putting into array:
countif(Subj in out_sht is also in Query_sht, AND, DS value in Query_sht is less than or equal to DS value in out_sht, AND , Status col in Query_sht equals "Open")
+
countif(Subj in out_sht is also in Query_sht, AND, DS value in Query_sht equals "All", AND , Status col in Query_sht equals "Open")




Second:
output_answeredqueries_array(L1, 1) = Application.WorksheetFunction.CountIfs( _
query_subj_range, output_subj_array(L1, 1), _
query_segID_range, "<=" & output_datasegment_array(L1, 1), _
query_qrystatus_range, "Answered") + _
Application.WorksheetFunction.CountIfs( _
query_subj_range, output_subj_array(L1, 1), _
query_segID_range, "All", _
query_qrystatus_range, "Answered")


calculating value and putting into array:
countif(Subj in out_sht is also in Query_sht, AND, DS value in Query_sht is less than or equal to DS value in out_sht, AND , Status col in Query_sht equals "Answered")
+
countif(Subj in out_sht is also in Query_sht, AND, DS value in Query_sht equals "All", AND , Status col in Query_sht equals "Answered")

i posted what i am counting.........this is what i am counting.........not sure how else to explain...........

anyone got any ideas?

if you have any questions just let me know............

ahhhhhhhhhh so sorry.........

LOTID is the same as SubjectID

so subj is basicallyl the first col on each of teh sheets that contain data.

i was able to get it down from 43sec to 12sec........

take a look at the code in the 2nd module.....the code in the first module contains the original code which takes 43sec.

the code in the 2nd module does use a scripting dic ......but i still think it can be made faster........

any thoughts or suggestions?

anyone have any suggestions?

Hi Welchs,
try

Please Login or Register  to view this content.

wow........nilem, that is fast.....takes only 0.5 sec......

i will have to review the code a bit to try and understand it but its a good learning experience.......

very fast!!!!!!

thanks!!!!!!!

norie, thanks for your help as well!

nilem,
in reviewing your code i am trying to understand what your doing here (see below):

Please Login or Register  to view this content.

also, what is "Array(i,i)"

ok....i think i know what the answer was to my previous question

'this adds/loads keys into dictionary
'it also adds values to the dictionary
'which corresponds to the rows of the duplicates
'for example if 'junk' appears in rows 2 through 11
'then the values in the dictionary for this key
'is 2 and 11

is this right?

Yes, something like that.
By the way, I think there is an extra line in my code: If y(j, 1) = x(i, 1) Then with the corresponding End If
Try to remove it

nilem,

i was just going to as you about that line..........

Please Login or Register  to view this content.

so the line designated with the '**Question**

this is not really needed......right?

Yeah, right

nilem,
right now when i run the macro i get "empty" cells.........how do i make those empty cells zero's

nilem,

i added two lines of code.....

Please Login or Register  to view this content.

this does replace empty cells with 0's..........is this the right place?

Hi David,
try

Please Login or Register  to view this content.

ok...so i ran your new code and it works. I am just now trying to figure out why.

ok....so as far as i can tell the only difference which would account for the "0's" being added is

in the first macro you had:

Please Login or Register  to view this content.

in the 2nd macro you had:

Please Login or Register  to view this content.

but i dont get why the "&" would change anything........

so is the ampersand just declaring the array as a type Long.............

and is this why all the elements in the array are now not blank but 0

David, I'm glad you're interested
Try this to make it clearer

Please Login or Register  to view this content.

thanks.......i get it now.........

hey, one more question......

if i remove the "dim" statement for the variable "t" then i get a run time error........subscript out of range at this step in the code

Please Login or Register  to view this content.

if i leave the declaration statement

Please Login or Register  to view this content.

the code runs fine any ideas?

But why do you remove a variable from a string variable declarations? If you use the Option Explicit operator then your code will not work against such action.