Hi
I wrote a macro that supposed to sync 2 worksheets.
Each worksheet has 5 tabs and each tab has about 150 columns and 500 rows
The columns and rows are in a different order.
The macro works but very slowly and the computer does not respond until the macro end.
When I run the macro from the editor the macro run very fast.
I use all the known tips like screen update, switching to manual calculation mode, setting variables, etc.
I understand that reading values from Excel to VBA slows down the macro
I try to solve this by handling the synchronization of a column instead of synchronizing each cell individually.
Trying to use an array to read a set of values from a column something like
mymatch= evaluate("if (srcsht!a1:a500=trgtsht!d1:d500,srcsht!a1:a500, "no update")")
trgtsht!d1:d500=mymatch
It improves running time but not significantly
The question is is it possible to do the same thing in an array without using a loop?
arrsrc=srcsht!a1:a500
arrtrgt=trgtsht!d1:d500
mymatch_arr= evaluate("if (arrsrc=arrtrgt,arrsrc, "no update")")
trgtsht!d1:d500=mymatch_arr
thanks
Bookmarks