I wrote a routine to do the following – and it works fine, but I was
trying to be a bit elegant with my code. Can anyone give me some
direction if the way I wanted to program the routine can be done.

Here’s the problem I was coding a solution for:

I have an array that contains a list of products. For each customer, a
variable number of unique products are pulled and the pull order has
to be maintained. Additionally, the products are grouped for each
customer. So, a code in the form of “X.YY” is created for each
customer, where X is the group 1, 2, 3 … alpha, beta, etc… (alphas
have to follow numerics) . YY is the sequential order of the products
for each individual customer.

So, the data array has the product list going down the rows, while
each column is the specific customer with the X.YY code against those
products that comprise that order.

When I print out any customer’s order, the required format is a
heading for each group (starting with numerics, e.g. “Group 1, Group
2, etc”) and the product list in the sequential order. I accomplished
this by sorting the specific customer column in ascending order,
reading each product into a temporary array (along with the X.YY
code), then running each row of the temp array through a series of
'if' statements to print the product list. As each group is changed
(“X”), a new section header (“Group “ & X) would be printed, followed
by the product list. If the group was an alpha, it would just print
the alpha.

I got the code running with no problems using a number of if
statements (if X= number, if X= alpha, etc) and a lot of redundant
lines of code, but I was hoping to program something that would
recognize the change in group designation and make a few branch
changes, without a whole lot of software repeated.

Any suggestions?

Art

post your code for observation

--

Regards,
Nigel




"c1802362" wrote in message
...
I wrote a routine to do the following – and it works fine, but I was
trying to be a bit elegant with my code. Can anyone give me some
direction if the way I wanted to program the routine can be done.

Here’s the problem I was coding a solution for:

I have an array that contains a list of products. For each customer, a
variable number of unique products are pulled and the pull order has
to be maintained. Additionally, the products are grouped for each
customer. So, a code in the form of “X.YY” is created for each
customer, where X is the group 1, 2, 3 … alpha, beta, etc… (alphas
have to follow numerics) . YY is the sequential order of the products
for each individual customer.

So, the data array has the product list going down the rows, while
each column is the specific customer with the X.YY code against those
products that comprise that order.

When I print out any customer’s order, the required format is a
heading for each group (starting with numerics, e.g. “Group 1, Group
2, etc”) and the product list in the sequential order. I accomplished
this by sorting the specific customer column in ascending order,
reading each product into a temporary array (along with the X.YY
code), then running each row of the temp array through a series of
'if' statements to print the product list. As each group is changed
(“X”), a new section header (“Group “ & X) would be printed, followed
by the product list. If the group was an alpha, it would just print
the alpha.

I got the code running with no problems using a number of if
statements (if X= number, if X= alpha, etc) and a lot of redundant
lines of code, but I was hoping to program something that would
recognize the change in group designation and make a few branch
changes, without a whole lot of software repeated.

Any suggestions?

Art

On Mar 1, 2:52 am, "Nigel" wrote:
post your code for observation

here's the relevant code followed by a sample of the database. (all
the formatting and indentations were lost) . The area I'm interested
in improving are lines 51-75

1 Option Explicit
2 Sub BuildList()
3
4 Dim CustomerName As String, Addr As String
5 Dim i As Integer, j As Integer, k As Integer
6 Dim ProductMatrix(200, 2)
7 Dim Flag As Boolean
8
9 Sheets("Current").Select 'switch to master
10
11 Range("A1").Activate
12
13 CustomerName = BuildChartSet.GigList.Value
14
15 ' find the CustomerName selected
16 Cells. Find(What:=CustomerName, After:=ActiveCell,
Lookln:=xIFormulas, _
17 LookAt:=xIPart, SearchOrder:=xIByRows, SearchDirection:=xINext,_
18 MatchCase:=False, SearchFormat:=False).Activate
19
20 Addr = ActiveCell.Address 'create key that sorts on desired
column
21
22 ActiveCell.CurrentRegion.Sort Key1 :=Range(Addr),
Order1 :=xIAscending, Header:= _
23 xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xITopToBottom, _
24 DataOption 1 :=xISortTextAsNumbers
25
26 i = 1 ' initalize counters
27 j = 1
28 k = 1
29
30 Do Until ActiveCell = "" , count number of entries in column
31 ProductMatrix(k, 1) = Cells(k, 1) 'product name
32 ProductMatrix(k, 2) = Left(ActiveCell, 1) ' first character from
set code
33 ActiveCell.Offset(1, 0).Activate
34 k = k + 1
35 Loop
36
37 Workbooks.Add 'create new worksbook
38 Sheets("Sheet1").Select
39
40 For j = 1 To k 'write transfer array onto new worksheet
41 Cells(j, 1) = ProductMatrix(j, 1) ' product name
42 Cells(j, 2) = ProductMatrix(j, 2) ' group (1 ,2,3,Alternate,etc)
43 Nextj
44
45 j = 1
46 k = 1
47 Range("A1").Activate 'starting row of list
48
49 Flag = False ' set flag for when pointer reaches alpha group
50
51 Do Until ActiveCell = ""
52
53 If Cells(j, 2) = k Then I inset line with "group" and group
54 Selection.EntireRow.lnsert' number above each section
55 Selection. Font. Bold = True
56 ActiveCell(1, 1) = "Group" & k
57 ActiveCell.Offset(1, 0). Activate
58 k = k + 1
59 Else
60 If Flag = True Then GoTo jump 'bypass alpha
61 If Cells(j, 2) = "A" Then ' inset line with alpha above alpha
section
62 Selection. EntireRow. Insert
63 Selection.Font.Bold = True
64 ActiveCell( 1, 1) = "Alternate"
65 ActiveCell.Offset(1, 0).Activate
66 'after first product marked "alternate" is found, set flag to skip
future instances
67 Flag = True
68 End If
69 End If
70
71 j = j + 1 ' advance pointer
72
73 jump: ActiveCell.Offset(1, 0).Activate
74
75 Loop
76
77 Columns("B:B").Activate
78 Selection. Delete Shift:=xIToLeft 'delete column with X.YY
codes
79
80 End Sub

the database: (each ":" is a tab to align up under each column)

Product:Customer 1:Customer 2:Customer 3:Customer 4:Customer 5
product 1:2.04:A.06:A.05::
product 2:2.05::::
product 3:::2.04::
product 4:2.07:::3.04:4.02
product 5:2.14::A.06::
product 6:A.01::1.04::1.02
product 7:::::1.04
product 8:A.05:::A.02:A.03
product 9:1.06:A.01:4.02::
product 10::::1.01:2.01
product 11::1.04:::
product 12:::1.01::
product 13:1.02:2.06:3.03:2.01:2.04
product 14:1.03:2.07:3.04::
product 15::::3.10:A.01
product 16:A.04::::
product 17::::A.08:
product 18:2.06::::
product 19::::3.05:4.03
product 20::::3.02:4.01
product 21:::2.01::
product 22::2.01:1.06::
product 23::::3.01:3.02
product 24:1.04:2.11:4.01::
product 25:1.05:2.12:2.02:3.03:
product 26::2.03:::
product 27:A.03::::
product 28:::A.08::
product 29:::A.09::
product 30:::1.05:A.03:A.04
product 31:2.02:A.04:A.03::
product 32::1.03:2.05::
product 33::::A.04:
product 34:::3.02::
product 35:2.08:::A.05:
product 36::::1.03:2.02
product 37::::1.02:1.03
product 38::::1.04:
product 39:2.03:A.05:A.04:A.07:
product 40:::1.03::
product 41::1.01:1.02::
product 42::::A.09:
product 43::::A.10:1.01
product 44::1.02:::
product 45:::3.01::
product 46:1.01:2.05:1.07::
product 47::::A.01:A.02
product 48:::A.07::
product 49:::::1.05
product 50:1.08:2.02:::
product 51::2.04:::
product 52:A.02::::
product 53:::::
product 54::::1.05:2.03
product 55:2.01:A.03:A.02::
product 56::::2.03:3.01
product 57:1.07:A.02:A.01::
product 58:::::
product 59::::A.06:
product 60:::2.03::
product 61::::2.02:2.05