| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
slow for loops ... better way using arrays or something?
I am (still) dealing with slow running code.
The following use "for" loops to populate cells. The first example needs add headers to thrtee sheets based on what exists on another sheet. The second example needs to add a formula to each row in column A that contains values in column B. Could someone suggest a better way to get these jobs done? Arrays? ****************** 1. For index1 = 1 To num_headers 'num headers is variable header_val = Worksheets("sheet1").Cells(1, index1).Value Worksheets("sheet2").Cells(1, index1).Value = header_val Worksheets("sheet3").Cells(1, index1 + 1).Value = header_val Worksheets("sheet4").Cells(1, index1 + 1).Value = header_val Next 2. For index1 = 1 To num_month_records 'variable Worksheets("sheet1").Cells(index1 + 1, 1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" Next |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
slow for loops ... better way using arrays or something?
Dim header_val as Variant
header_val = Worksheets("sheet1").Cells(1, 1).Resize(1,num_headers).Value Worksheets("sheet2").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet3").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet4").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet1").Cells(2, 1).Resize( _ num_month_records,1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" -- Regards, Tom Ogilvy "wdeleo" wrote in message ... I am (still) dealing with slow running code. The following use "for" loops to populate cells. The first example needs add headers to thrtee sheets based on what exists on another sheet. The second example needs to add a formula to each row in column A that contains values in column B. Could someone suggest a better way to get these jobs done? Arrays? ****************** 1. For index1 = 1 To num_headers 'num headers is variable header_val = Worksheets("sheet1").Cells(1, index1).Value Worksheets("sheet2").Cells(1, index1).Value = header_val Worksheets("sheet3").Cells(1, index1 + 1).Value = header_val Worksheets("sheet4").Cells(1, index1 + 1).Value = header_val Next 2. For index1 = 1 To num_month_records 'variable Worksheets("sheet1").Cells(index1 + 1, 1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" Next |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
slow for loops ... better way using arrays or something?
Well ... THAT's better. It was taking several minutes and now it takes about
30 seconds. Still unsatisfactory, but at least now I can run it and disect it at a reasonable pace. I think there is still something that needs to be fixed as well as the parts that could use improvement. Thanks soooo much Tom (again) Billy "Tom Ogilvy" wrote: Dim header_val as Variant header_val = Worksheets("sheet1").Cells(1, 1).Resize(1,num_headers).Value Worksheets("sheet2").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet3").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet4").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet1").Cells(2, 1).Resize( _ num_month_records,1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" -- Regards, Tom Ogilvy "wdeleo" wrote in message ... I am (still) dealing with slow running code. The following use "for" loops to populate cells. The first example needs add headers to thrtee sheets based on what exists on another sheet. The second example needs to add a formula to each row in column A that contains values in column B. Could someone suggest a better way to get these jobs done? Arrays? ****************** 1. For index1 = 1 To num_headers 'num headers is variable header_val = Worksheets("sheet1").Cells(1, index1).Value Worksheets("sheet2").Cells(1, index1).Value = header_val Worksheets("sheet3").Cells(1, index1 + 1).Value = header_val Worksheets("sheet4").Cells(1, index1 + 1).Value = header_val Next 2. For index1 = 1 To num_month_records 'variable Worksheets("sheet1").Cells(index1 + 1, 1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" Next |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
slow for loops ... better way using arrays or something?
Running this code on 60000 rows takes less than 1 second.
So the answer lies elsewhere with some other code or maybe .Calculation. NickHK "wdeleo" wrote in message ... Well ... THAT's better. It was taking several minutes and now it takes about 30 seconds. Still unsatisfactory, but at least now I can run it and disect it at a reasonable pace. I think there is still something that needs to be fixed as well as the parts that could use improvement. Thanks soooo much Tom (again) Billy "Tom Ogilvy" wrote: Dim header_val as Variant header_val = Worksheets("sheet1").Cells(1, 1).Resize(1,num_headers).Value Worksheets("sheet2").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet3").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet4").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet1").Cells(2, 1).Resize( _ num_month_records,1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" -- Regards, Tom Ogilvy "wdeleo" wrote in message ... I am (still) dealing with slow running code. The following use "for" loops to populate cells. The first example needs add headers to thrtee sheets based on what exists on another sheet. The second example needs to add a formula to each row in column A that contains values in column B. Could someone suggest a better way to get these jobs done? Arrays? ****************** 1. For index1 = 1 To num_headers 'num headers is variable header_val = Worksheets("sheet1").Cells(1, index1).Value Worksheets("sheet2").Cells(1, index1).Value = header_val Worksheets("sheet3").Cells(1, index1 + 1).Value = header_val Worksheets("sheet4").Cells(1, index1 + 1).Value = header_val Next 2. For index1 = 1 To num_month_records 'variable Worksheets("sheet1").Cells(index1 + 1, 1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" Next |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
slow for loops ... better way using arrays or something?
Just to expand, Nick is suggesting you might try
Application.Calculcation = xlManual ' current code ' optionally change it back to automatic Application.Calculation = xlAutomatic -- Regards, Tom Ogilvy "NickHK" wrote: Running this code on 60000 rows takes less than 1 second. So the answer lies elsewhere with some other code or maybe .Calculation. NickHK "wdeleo" wrote in message ... Well ... THAT's better. It was taking several minutes and now it takes about 30 seconds. Still unsatisfactory, but at least now I can run it and disect it at a reasonable pace. I think there is still something that needs to be fixed as well as the parts that could use improvement. Thanks soooo much Tom (again) Billy "Tom Ogilvy" wrote: Dim header_val as Variant header_val = Worksheets("sheet1").Cells(1, 1).Resize(1,num_headers).Value Worksheets("sheet2").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet3").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet4").Cells(1, 1).Resize(1,num_headers).Value = header_val Worksheets("sheet1").Cells(2, 1).Resize( _ num_month_records,1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" -- Regards, Tom Ogilvy "wdeleo" wrote in message ... I am (still) dealing with slow running code. The following use "for" loops to populate cells. The first example needs add headers to thrtee sheets based on what exists on another sheet. The second example needs to add a formula to each row in column A that contains values in column B. Could someone suggest a better way to get these jobs done? Arrays? ****************** 1. For index1 = 1 To num_headers 'num headers is variable header_val = Worksheets("sheet1").Cells(1, index1).Value Worksheets("sheet2").Cells(1, index1).Value = header_val Worksheets("sheet3").Cells(1, index1 + 1).Value = header_val Worksheets("sheet4").Cells(1, index1 + 1).Value = header_val Next 2. For index1 = 1 To num_month_records 'variable Worksheets("sheet1").Cells(index1 + 1, 1).FormulaR1C1 = "=CONCATENATE(RC3,""_"",RC4)" Next |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA excel using arrays and loops | Excel Programming | |||
| Formula Arrays VERY SLOW in Excel 2002 | Excel Worksheet Functions | |||
| Scope of the arrays in Loops | Excel Programming | |||
| Arrays to replace very slow loops ? | Excel Programming | |||
| Excel 2000 Slow Loops | Excel Programming | |||
![wdeleo[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
