Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through data and Populate template
Hi all,
I have a question about loop procedures. I have 2 ranges that contain the following info Range 1: PackageNames Package1 $20.00 Package2 $30.00 Range 2: PackageDetail Package1 Product 1 Package1 Product 2 Package1 Product 3 Package1 Product 4 Package2 Product 2 Package2 Product 3 Package2 Product 5 Package2 Product 6 And I use the following code to pick out the info in these 2 ranges and place them in appropriate location in a tamplate, which is in a difference tab. The template looks like this. Package1 -----------------PackPrice ------------------------------------------------- Product1 Product2 Product3 Product4 In this template, the "Product.." area is a large range called "TmplPackDetail" Here is my code, which don't work...:( . This code is intented to loop through the two ranges, save their content to 2 arrays, then compare the values. When the package name in Array 1 = package name in array 2, set the value of first cell in the template = to the first product in the range2. Then repeat it self. Ideally, when the packname are not equal, I want excel to export the template to MS Word, then clear template, then run the same loop again to populate a diffent package.But we are not there yet, since the first part of the code is not even working. I'm familiar with Access coding, not Excel. I really need help with this. I really appreciate your help and time. Sub Template() 'Generate PackNames Array Dim PackageName() As String Dim intPackNCount As Integer intPackNCount = Range("PackageNames").Rows.Count ReDim PackageName(intPackNCount) Dim i As Integer For i = 1 To intPackNCount PackageName(i) = Range("PackageNames").Cells(i + 1, 1).Value Next i 'Generate PackDetails Array Dim PackageDName() As String Dim intPackDCount As Integer intPackDCount = Range("PackageDetails").Rows.Count ReDim PackageDName(intPackDCount) Dim j As Integer For j = 1 To intPackDCount PackageDName(j) = Range("PackageDetails").Cells(j + 1, 1).Value Next j 'If arguments For i = 1 To intPackCount For j = 1 To intPackDCount If PackageName(i) = PackageDName(j) Then Range("TmplPackDetail").Cells(i + 1, 1).Value = PackageDName(j) Else Exit Sub End If Next j Next i End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through data and Populate template
nxqviet wrote:
Hi all, I have a question about loop procedures. I have 2 ranges that contain the following info Range 1: PackageNames Package1 $20.00 Package2 $30.00 Range 2: PackageDetail Package1 Product 1 Package1 Product 2 Package1 Product 3 ' Package1 Product 4 Package2 Product 2 Package2 Product 3 Package2 Product 5 Package2 Product 6 And I use the following code to pick out the info in these 2 ranges and place them in appropriate location in a tamplate, which is in a difference tab. The template looks like this. Package1 -----------------PackPrice ------------------------------------------------- Product1 Product2 Product3 Product4 In this template, the "Product.." area is a large range called "TmplPackDetail" Here is my code, which don't work...:( . This code is intented to loop through the two ranges, save their content to 2 arrays, then compare the values. When the package name in Array 1 = package name in array 2, set the value of first cell in the template = to the first product in the range2. Then repeat it self. Ideally, when the packname are not equal, I want excel to export the template to MS Word, then clear template, then run the same loop again to populate a diffent package.But we are not there yet, since the first part of the code is not even working. I'm familiar with Access coding, not Excel. I really need help with this. I really appreciate your help and time. Sub Template() 'Generate PackNames Array Dim PackageName() As String Dim intPackNCount As Integer intPackNCount = Range("PackageNames").Rows.Count ReDim PackageName(intPackNCount) Dim i As Integer For i = 1 To intPackNCount PackageName(i) = Range("PackageNames").Cells(i + 1, 1).Value Next i 'Generate PackDetails Array Dim PackageDName() As String Dim intPackDCount As Integer intPackDCount = Range("PackageDetails").Rows.Count ReDim PackageDName(intPackDCount) Dim j As Integer For j = 1 To intPackDCount PackageDName(j) = Range("PackageDetails").Cells(j + 1, 1).Value Next j 'If arguments For i = 1 To intPackCount For j = 1 To intPackDCount If PackageName(i) = PackageDName(j) Then Range("TmplPackDetail").Cells(i + 1, 1).Value = PackageDName(j) Else Exit Sub End If Next j Next i End Sub Hi, Try this out on a copy of you data. I've added one more named range for receiving the Package Name and Package Cost. Hopefully my code comments make sense. Public Sub Template2() 'generate PackageName array, a 2-dim variant array that actually only 'has 1 column. Its elements must be addressed as PackageName(index,1) Dim PackageName As Variant PackageName = Range("PackageNames") 'generate PackageDName array, a 2-dim variant array that has 2 columns. 'Package names are addressed as PackageDName(Index,1) and 'Products within a package as PackageDName(Index,2) Dim PackageDName As Variant PackageDName = Range("PackageDetails") Dim I As Long Dim J As Long Dim K As Long For I = 1 To UBound(PackageName) 'I used a named range (Name:="TmplPackage") consisting of two 'horizontally adjacent cells. The left cell is for the Package Name 'and the right cell is for the Package cost. I located this named range 'just above the named range named "TmplPackDetail" With Range("TmplPackage") ..Cells(1, 1) = PackageName(I, 1) 'Package name ..Cells(1, 1) = PackageName(I, 1) 'Package Cost End With K = 0 For J = 1 To UBound(PackageDName) If PackageDName(J, 1) = PackageName(I, 1) Then K = K + 1 Range("TmplPackDetail").Cells(K).Value = PackageDName(J, 2) End If Next J 'the following MsgBox just pauses the code so that you can see that '(hopefully) it is working. If you run the code from the sheet 'receiving the processed data then, after clicking OK on the 'MsgBox, you should see the next Package Name and Cost 'with a list of its Products. The next part of your code involving Word 'should replace the MsgBox. MsgBox "" Next I End Sub Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate a template for each customer | Excel Discussion (Misc queries) | |||
Populate central worksheet template from multiple worksheets | Excel Discussion (Misc queries) | |||
HOW DO I POPULATE AN EXCEL TEMPLATE USING DATABASE | Excel Worksheet Functions | |||
How can I have Excel cells populate a Word label template? | Excel Discussion (Misc queries) | |||
How do I populate 2006 calendar into the attendance template. | Excel Worksheet Functions |