How to make faster. Using Worksheetfunction.Countifs

I have this code (see below). Is there a way to make this faster or is there a Better way to do this? If not thats ok.......just wondering.

Please Login or Register  to view this content.

The alternative, it may better for you, plz test it because of I have no sample file to test

Please Login or Register  to view this content.

i will check them out . I will probably have questions............

I am trying to check the suggestions out.....running into issues. Can get code to compile.

I created a test file to make it easier to see what i am trying to do. Sorry for not doing this earlier.

THe original file i tried to upload had 96K rows......had to shorten to meet the max file size requirements.

i just realized that when i made the file smaller to get it to post here i changed the name which caused an error in addition to the error i was asking about. so i have corrected the name issue but i still get "application-defined object defined error".

here is the code which is causing the error

Please Login or Register  to view this content.

still cant figure out the error............

any ideas what this is doing

Please Login or Register  to view this content.

It is a very nice thing to see your excellent work and I like your article very much. With your rich knowledge, we can learn more from your wonderful post. Thanks so much.

tigertiger and snb,

I tried both of your suggestions and

snb: I cant get yours to compile

tigertiger: Yours gives a run time error.

Anyideas?

romperstomper thanks for replying.

In the file i sent out, the entire code is shown in the file.

In the file i define

Sheet_DCF_Inputfile="Sheet_DCF_Inputfile"

this should work.........right?

if you have time i would like to understand the approach of tigertiger but also if you have alternative approach i would like to understan that as well.

thanks.

I will check out the code you sent in just a bit. Can tigertigers code be changed from r1c1 to someother type to make it work? thanks.

romperstomper,

oh really. For some reason i was thinking that tigertiger's soln did not require a for loop and if it did not then it looked like it would go faster. Again, i did not understand tigertiger's code but i was trying to. From what i could tell it looked like tigertiger was trying to insert formulas thoughout a range.

Just curious then how tigertiger's code would be faster than mine if its nearly the same as mine. Are there somethings in my code which could be made faster?

romperstomper, your so right. i appologize.

thanks for your helpl!