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
"Bob Phillips" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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)
>
>
>
> "Conan Kelly" <(E-Mail Removed)> wrote in message
> news:I4k8j.254843$(E-Mail Removed)...
>> 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
>>
>>
>>
>>
>> "Bob Phillips" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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)
>>>
>>>
>>>
>>> "Conan Kelly" <(E-Mail Removed)> wrote in message
>>> news:Rij8j.254651$(E-Mail Removed)...
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>> "Conan Kelly" <(E-Mail Removed)> wrote in message
>>>> news:XTi8j.44203$(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "Steve" <(E-Mail Removed)> wrote in message
>>>>> news:C14CA237-1698-4D12-911F-(E-Mail Removed)...
>>>>>> 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.
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>
|
Similar Threads
