More efficient way to find max value for an array of values

[welchs101]

Hi,

I have a macro that works.........but it takes ~23sec to run. I am trying to find a quicker more efficient way to do the same thing. I am thinking a scripting dictionary way might be the answer.

What macro is supposed to do:
See "Data_And_Output" worksheet. Three cols of data on this sheet.

SubjectIDs: List of subject values
Expected: Expected value for each subject
Max Expected: This is the column the macro calculates. Maximum expected value for subject value listed in col-A.

NOTE: In order to attach file i had to reduce the number of rows.......originally number of rows was 108770. BUt i am reducing it to 70,000 rows

[romperstomper]

Try this:

Please Login or Register  to view this content.

[welchs101]

thanks romperstomper............

do you also know a scripting dictionary approach.

i am trying to learn how to use the scipting dic and trying to apply it to many of the scenarios i run into.

thanks.

[romperstomper]

Seems unnecessary here, but:

Please Login or Register  to view this content.

would be one way.

[nilem]

as an option

Please Login or Register  to view this content.

[welchs101]

first method using R1C1 formula takes ~ 2sec
scripting method takes ~0.6sec
nilems sort/arrays ~ 0.2sec


i am surprised that the scripting method is slower than the sort/array method...........

[AB33]

In the first code, there is no loop. No matter how scripting could be fast, it can not be faster than non looping code, for e.g. filter. You can only compare scripting with looping though a range.