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
|