code to create list of values

S

Steve

a1:a50 is a list of item keys. b1:b23 is a list of counties. I want to
create a two column list (it would have 1150 rows: 50 x 23). It would have
every county for each item key.
 
C

Conan Kelly

Steve,

Where do you want this list to be?

(be sure to make a copy of your file and work on the copy!!!)
(assuming you have the sheet that has these 2 lists as the active sheet in
the active workbook)
The following code will create the results you desire on a sheet named
"Sheet2" complete with column headings (I THINK!!! I have not tested it.)

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

prngItemKeys = ActiveSheet.Range("A1:A50")
prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "Item Key"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintcoutner = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub

HTH,

Conan
 
B

Bob Phillips

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastItem As Long
Dim LastCounty As Long

With ActiveSheet

LastItem = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCounty = .Cells(.Rows.Count, "B").End(xlUp).Row
For i = 1 To LastItem * LastCounty Step LastItem
j = j + 1
.Cells(1, "A").Resize(LastItem).Copy .Cells(i, "M")
.Cells(i, "N").Resize(LastItem).Value = .Cells(j, "B")
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Conan Kelly

Woops,

There were a couple of errors in that code I posted.

I corrected them and tested it and it looks like it works!!!

Option Explicit

Sub CreateCombinedList()
Dim prngItemKeys As Range
Dim prngCounties As Range
Dim prngItemKeyCell As Range
Dim prngCountyCell As Range
Dim pintCounter As Integer

Set prngItemKeys = ActiveSheet.Range("A1:A50")
Set prngCounties = ActiveSheet.Range("B1:B23")
pintCounter = 2

Worksheets("Sheet2").Range("A1") = "Item Key"
Worksheets("Sheet2").Range("B1") = "County"

For Each prngItemKeyCell In prngItemKeys.Cells
For Each prngCountyCell In prngCounties.Cells
Worksheets("Sheet2").Cells(pintCounter, 1) =
prngItemKeyCell.Value
Worksheets("Sheet2").Cells(pintCounter, 2) =
prngCountyCell.Value
pintCounter = pintCounter + 1
Next prngCountyCell
Next prngItemKeyCell


End Sub
 
B

Bob Phillips

But still 9 times slower than mine <vbg>

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Conan Kelly

Bob,

What is "<vbg>"?

Why is this way so much slower than your way? What bogs it down so much?

Considering the result is only 1150 rows, is anyone going to notice the
difference? ;-)

Thanks,

Conan
 
B

Bob Phillips

Hi Conan,

<vbg> is Very Big Grin, I was ribbing you.

It is so much slower because you have a loop within a loop, I have a single
loop that loads them in blocks rather than another inner loop.

In this case, it is not discernible, but performance should always be borne
in mind, as it all mounts up and suddenly, it hurts.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
C

Conan Kelly

Bob,

Thanks for the info, ej-ja-ma-ca-tion, and the ribbing.

Unfortunately, I don't think I will ever get the chance to rib you :'(
(<--don't know what the usenet equiv. of that is......maybe <c>.......maybe
<cf>.......maybe <vbf>)

;-)

Conan
 
Top