Database Reference
In-Depth Information
Dim arrValidChn() As String
Dim arrValidSeg() As String
ReDim Preserve arrValidEnt(100)
ReDim Preserve arrValidChn(100)
ReDim Preserve arrValidSeg(100)
Sheets("Sheet1").Activate
cellCount = 0
With Sheets("Sheet1")
' We created a named range called "data"
For Each cell In Range("data")
'Make sure the cell has text, otherwise don't add it
If Len(cell.Value) > 0 Then
' cellCount keeps track of how many lookups we've
' added to the collections
cellCount = cellCount + 1
' rather than redim the array every time,
' we redim it in increments of 100 to improve
performance
If (cellCount Mod 100 = 0) Then
ReDim Preserve arrValidEnt(cellCount + 100)
ReDim Preserve arrValidChn(cellCount + 100)
ReDim Preserve arrValidSeg(cellCount + 100)
End If
' add the member of each dimension to its collection
' (The corresponding subscript of each collection
' represents one valid combination of the sparse
members)
arrValidEnt(cellCount - 1) = Cells(1, cell.Column).
Value
arrValidChn(cellCount - 1) = Cells(cell.row, 2).
Value
arrValidSeg(cellCount - 1) = Cells(cell.row, 1).
Value
End If
Next cell
End With
' Activate a different (blank) sheet to layout the collections
' We'll use this data for lookups later on. We name the ranges
to
' make the lookup code we'll write later easier to read.
Sheets("Sheet2").Activate
With Application.WorksheetFunction
Range("a1:a" & cellCount).Value = .Transpose(arrValidSeg)
Range("a1:a" & cellCount).Name = "Segment"
Range("b1:b" & cellCount).Value = .Transpose(arrValidChn)
Range("b1:b" & cellCount).Name = "Channel"
Range("c1:c" & cellCount).Value = .Transpose(arrValidEnt)
Range("c1:c" & cellCount).Name = "Entity"
End With
End Sub
Programmatically, we can manage the Pov selections on our worksheet by using
choices made by the user from a dropdown selection box. In this case, each dropdown
