Combination Macro for =LARGE & INDEX/MATCH

**mabeaver** · 04-06-2012, 06:19 PM

I need help writing the VBA for a macro that will pull the largest values from a data set in one worksheet, place them in another worksheet, and also apply the date corresponds to the large value. The end use is for insurance loss runs. Here is how I have my two sheets set up:

Sheet1 has 2 arrays(YEAR1 & YEAR2). In the first column of each array I have the date a loss occured. In the second column, the dollar amount of the loss (which I designated Loss1 & Loss2).

Sheet 2 has a cell for a loss limit that can be amended as necessary. The table in sheet2 should pull the 3 largest losses from the 2 arrays in sheet1, with the largest at the top. If the user enters a new loss limit, I would like the macro, once run again, to update the large loss based on this limit. In addition, I would like the macro to pull the corresponding dates from these large losses from the arrays in sheet1.

This is an oversimplification of my final product, but I would appreciate the help. I have attached excel workbook, and below I have listed the VBA that I have that seems work well in pulling just the largest losses:

Please Login or Register  to view this content.

**venkat1926** · 04-07-2012, 12:16 AM

what is the loss limit in sheet2. is it the lowest limit? that is the largest 3 losses should be more than loss limit. clarify

**venkat1926** · 04-07-2012, 03:59 AM

try this macro (on assumption given by my earlier message)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

**mabeaver** · 04-09-2012, 01:31 PM

The loss limit should be floating - in other words, the user should be able to amend the loss limit to whatever value he/she wishes.

The code you posted worked pretty well. It pulled the correct dates associated with the losses in sheet1, but when I attempted to change the Loss Limit, it no longer pulled the correct losses. For example, when I changed the limit from $5,000 to $9,000 it pulled losses under the $9,000 limit (10K,5K, 4K).

**mabeaver** · 04-09-2012, 02:16 PM

It might help if I attach the actual tool I'm working on. I've unprotected the cells.

I would like for the user to be able to enter any Large Loss Limit they wish into cell C15 of WC Summary Tab. I would then like a Macro to find the losses greater than or equal to that value from the WC Input tab for all the years with losses, and add the corresponding date to the left of the Incurred column on the Summary tab (ex. if limit = $10,000, Summary tab would find Incurred values $100,000; 50,000; 15,000; and 10,000 in that order). User should be able to change limit from $10K to $20K for example, and it would eliminate the 15K and 10K values from the Large Claim Summary detail table.
I already have each Gross Incurred array from the Input tab designated with a name (WC_YEAR1;WC_YEAR2 etc), Date of Loss arrays named (WC_DATE1, WC_DATE2 etc).

Thanks for looking at this btw.