PDA

View Full Version : Creating a list of variables



Edi
07-10-2011, 11:49 PM
Hi,

I have a list of the following variables:

Geography: AU, NZ, UK, HK, TW, SING
Camp: Global,Local,Regional
Capacity:1,2,3,....,60

Is there a quick way to create a list of the possible combinations of these variables in vba? For example,

AU-Global-1
AU-Global-2
...
AU-Global-60
AU-Local-1
...
SING-Regional-60

Thanks!

Simon Lloyd
07-11-2011, 02:15 AM
That would be a lot of variables to keep in memory, you'd be better off with a hidden look up table, it really depends on how you want to use the variables as to how you create the look up table.

mohanvijay
07-11-2011, 08:18 PM
Try this




Dim d As String, e As String, f As String
Dim a As Variant, b As Variant, c As Variant
Dim i As Long, j As Long, k As Long, r As Long
r = 1
d = "AU,NZ,UK,HK,TW,SING"
e = "Global,Local,Regional"
f = "1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28, 29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53, 54,55,56,57,58,59,60"

a = Split(d, ",")
b = Split(e, ",")
c = Split(f, ",")
For i = 0 To UBound(a)
For j = 0 To UBound(b)
For k = 0 To UBound(c)
Cells(r, 1).Value = a(i) & "-" & b(j) & "-" & c(k)
r = r + 1
Next k
Next j
Next i