Manipulating Arrays Using VBA

[DRJD]

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

[Bob Phillips]

I would take the simple approach, the data will never be that large



Sub ProcessDat()
Dim aryStates As Variant
Dim aryPerils As Variant
Dim LastRow As Long
Dim LastCol As Long
Dim NextItem As Long
Dim i As Long, j As Long

With Worksheets("State Codes")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
ReDim aryStates(1 To LastRow - 1)
For i = 2 To LastRow

If IsError(Application.Match(.Cells(i, "A").Value, aryStates, 0)) Then

NextItem = NextItem + 1
aryStates(NextItem) = .Cells(i, "A").Value
End If
Next i
ReDim Preserve aryStates(1 To NextItem)
End With

With Worksheets("Associated Perils")

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
ReDim aryPerils(1 To (LastRow - 1) * (LastCol - 1))
NextItem = 0
For j = 2 To LastCol

For i = 2 To LastRow

If Not IsError(Application.Match(.Cells(i, "A").Value, aryStates, 0)) Then

If IsError(Application.Match(.Cells(i, j).Value, aryPerils, 0)) Then

If .Cells(i, j).Value <> "" Then
NextItem = NextItem + 1
aryPerils(NextItem) = .Cells(i, j).Value
End If
End If
End If
Next i
Next j
ReDim Preserve aryPerils(1 To NextItem)
End With

End Sub

[DRJD]

Thak you very much for your help xld.

It works perfectly.