Looping through data and Populate template

[nxqviet]

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

 

[Ken Johnson]

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