slow for loops ... better way using arrays or something?

[wdeleo]

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

 

[Tom Ogilvy]

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)"

 

[wdeleo]

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

 

[NickHK]

Running this code on 60000 rows takes less than 1 second.
So the answer lies elsewhere with some other code or maybe .Calculation.

NickHK

 

[Tom Ogilvy]

Just to expand, Nick is suggesting you might try

Application.Calculcation = xlManual

' current code

' optionally change it back to automatic
Application.Calculation = xlAutomatic