excel - Copy cells formulas VBA -

i did program in vba copy formulas in each cell in specific column, have 30501 points , program slow calculate 100 points, there better way so?

sub copyformulas()   dim integer dim cell range dim referencerange range dim string  = "$t$30510" set range1= activesheet.range("a1:a30510") set myrange = range("t16:t30510") = 16  until cells(20, 30510)     range1         each cell in myrange             if cell.hasformula                 cells(i, 35).value = cell.address                 cells(i, 36).value = "'" & cstr(cell.formula)                 = + 1             end if         next     end loop end sub 

try adding following:

application.calculation = xlcalculationmanual application.screenupdating = false application.enableevents = false  ... code ...  application.calculation = xlcalculationautomatic application.screenupdating = true application.enableevents = true 

you may need first one, practice in using. also, using with ... end with statement? don't see use of in block.

it practice use option explicit @ top of module. , range1 , myrange not declared.

application.calculation 

when worksheet accessed or range's precedents has changed, excel automatically recalculate formulas on worksheet. since looping on 30,000 times, causes excel recalculate each time through loop and, thus, slows down performance.

application.screenupdating 

this line stops excel screen flashes , other things occur macro runs.

application.enableevents 

this line turns off events, such worksheet_change, event not triggered. if not turned off time change occurs on worksheet code in change event run. if have worksheet_selectionchange event code run every time select different cell. these events written in worksheet or workbook objects located in project window of vbe , there many events choose from. here simple illustration. place following in sheet1 object in project window:

private sub worksheet_selectionchange(byval target range)     msgbox "hi!" end sub 

now click around on worksheet. see responds each selection change. place following in regular module:

sub testenableevents()  application.enableevents = false activecell.offset(1, 0).select application.enableevents = true  end sub 

when run above code message box not triggered.