DRJD
07-01-2008, 03:00 AM
Good morning,
I am currently working on some VBA code which I would like to be able to produce an array which contains all the perils associated with the state codes listed in the "State Codes" column (Please see attached spreadsheet). I don't want the array to contain any duplicates (i.e. I only want "US Wind" in the array once no matter how many states are affected by "US Wind") which means that I need to filter the original "State Code" column for unique records and also make sure that the associated peril for each "State Code" is only added to the array if that peril is not already in the array.
For example, in the spreadsheet that I've attached, I want the VBA code to produce an array of associated perils which contains "US Wind", "Mid West Quake", "Southeast Quake", "New Madrid Quake", "Northeast Quake".
I've got as far as filtering the list using the advanced filter, but I am struggling with assigning arrays and then looping through them to produce the array that I want.
My current thinking is to create an array for each peril containing all the states affected by this peril, and then for each "State Code" in the filtered list, looping through the arrays to find which peril(s) the "State Code" is affected by and then adding the peril(s) to the final output array.
Is anyone able to help me, or at least give me a few pointers as to the most efficient way in which to code this.
Thanks
Dave
I am currently working on some VBA code which I would like to be able to produce an array which contains all the perils associated with the state codes listed in the "State Codes" column (Please see attached spreadsheet). I don't want the array to contain any duplicates (i.e. I only want "US Wind" in the array once no matter how many states are affected by "US Wind") which means that I need to filter the original "State Code" column for unique records and also make sure that the associated peril for each "State Code" is only added to the array if that peril is not already in the array.
For example, in the spreadsheet that I've attached, I want the VBA code to produce an array of associated perils which contains "US Wind", "Mid West Quake", "Southeast Quake", "New Madrid Quake", "Northeast Quake".
I've got as far as filtering the list using the advanced filter, but I am struggling with assigning arrays and then looping through them to produce the array that I want.
My current thinking is to create an array for each peril containing all the states affected by this peril, and then for each "State Code" in the filtered list, looping through the arrays to find which peril(s) the "State Code" is affected by and then adding the peril(s) to the final output array.
Is anyone able to help me, or at least give me a few pointers as to the most efficient way in which to code this.
Thanks
Dave