Looking for a more elegant way to program code
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 |
Looking for a more elegant way to program code
|
Looking for a more elegant way to program code
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 |
All times are GMT +1. The time now is 04:55 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com