excel - VBA slow allocation in long amount of data -

i'm trying develop vba macro excel find value (one o more occurrences) in quite large amount of data , copy value set of data. code is:

sub filaspallet() dim k long    worksheets("datos").range("e:f").clearcontents   application.screenupdating = false   application.calculation = xlcalculationmanual    k = 3   worksheets("datos").range("l:l")   while (k < (worksheets.count - 1) * 28 * 25)        set c = .find(worksheets("datos").cells(k, 3).value, searchdirection:=xlnext, searchorder:=xlbycolumns, lookin:=xlvalues, lookat:=xlwhole)       if not c nothing           firstaddress = c.address           oldpcount = 0                         pcount = c.offset(0, 5).value               d1 = k + oldpcount               d2 = k + oldpcount + pcount - 1               worksheets("datos").range("e" & d1 & ":e" & d2).value = c.offset(0, 3).value               worksheets("datos").range("f" & d1 & ":f" & d2).value = c.offset(0, 4).value               if pcount = 25                 goto nextiteration               end if               oldpcount = oldpcount + pcount               set c = .findnext(c)           loop while not c nothing , c.address <> firstaddress       else         worksheets("datos").range("e" & k & ":e" & k + 24).value = "no existe pallet"       end if nextiteration:       set c = nothing       k = k + 25    wend   end   application.screenupdating = true   application.calculation = xlcalculationautomatic   msgbox "acabado" end sub 

the main issues lines:

worksheets("datos").range("e" & d1 & ":e" & d2).value = c.offset(0, 3).value worksheets("datos").range("f" & d1 & ":f" & d2).value = c.offset(0, 4).value 

because if debug line line code, lines takes time finish value copy. whereas if use variation:

worksheets("datos").range("e" & d1 & ":e" & d2) = c.offset(0, 3).value worksheets("datos").range("f" & d1 & ":f" & d2) = c.offset(0, 4).value 

it works fine , takes short time expected in debug mode. when run whole program, despite runs fast, doesn't copy values.

could improve code or give me implementation idea?

thanks!

i solved problem if somehow in same trouble. using function find intensively make run slow. solve i've copied range array, accesing memory faster accesing worksheet takes less seconds in doing 1064 searches (within range of 1000 values) , each search paste 25 times string.

here's code:

sub filaspallet() dim k long dim pallets() variant dim palletname string    worksheets("datos").range("e:f").clearcontents   application.screenupdating = false   application.calculation = xlcalculationmanual    worksheets("datos")   pallets = application.transpose(.range("l1:l2000").value)   ub = ubound(pallets)   lb = lbound(pallets)    amountdata = .range("c3").end(xldown).row   k = 3   while (k < amountdata)     palletname = .cells(k, 3).value     oldpcount = 0     = lb ub         if pallets(i) = palletname             pcount = .cells(i, 17).value             d1 = k + oldpcount             d2 = k + oldpcount + pcount - 1             .range("e" & d1 & ":e" & d2) = .cells(i, 15)             .range("f" & d1 & ":f" & d2) = .cells(i, 16)             oldpcount = oldpcount + pcount             if oldpcount = 25                 goto break             end if         end if     next break:     if oldpcount <> 25         .range("e" & k & ":e" & k + 24).value = "no existe pallet"     end if     k = k + 25    wend   end    application.screenupdating = true   msgbox "completado! voy recalcular todas las formulas de la tabla. puede tardar un poco."   application.calculation = xlcalculationautomatic   msgbox "fin" end sub