excel - Arrange columns based on the order of values in an array - and buttons disappearing -

i have code looking in column (on different sheet @ xfd1) , creating array values in column. searching values 1 @ time across row on current sheet. when finds match, cuts column , inserts @ location corresponds order of values in array.

i'm not getting compile errors. placed button (not activex) on worksheet , used execute code. here see:

1. nothing appears happen. columns not moved @ all.
2. the computer "thinking " because whirly-gig spinning away.
3. and here mysterious part - button disappears! never comes back. placed several buttons on worksheet , tried them all. button disappears every time.

i need working. want reorder columns same order list on other sheet (95 items in list). thought code seem have entered twilight zone , things not seem (at least perspective)!

here is:

sub reorder_columns()     dim arrcolumnorder(1 95) string     dim index integer     dim found range     dim tick integer          index = 1 95             arrcolumnorder(index) = userformdropdowndatasheet.range("xfd1")         next index      application.screenupdating = false     tick = 1         index = lbound(arrcolumnorder) ubound(arrcolumnorder)            set found = rows("1:1").find(arrcolumnorder(index), lookin:=xlvalues, lookat:=xlwhole, _            searchorder:=xlbycolumns, searchdirection:=xlnext, matchcase:=false)                if not found nothing                    if found.column <> tick                    found.entirecolumn.cut                    columns(tick).insert shift:=xltoright                    application.cutcopymode = false                    end if                tick = tick + 1                end if         next index      application.screenupdating = true end sub 

the answer question concerning wrong original code is:

first, trying set size of array should have been working dynamic array expect data in array column grow add more columns sheet trying sort. so, dim arrcolumnorder(1 95) string should have been dim arrcolumnorder variant.

i trying iterate on array with

    index = 1 95         arrcolumnorder(index) = userformdropdowndatasheet.range("xfd1")     next index 

which of course wrong. replaced with

arrcolumnorder = userformdropdowndatasheet.range("xfd1:xfd95").value 

then, in

       set found = rows("1:1").find(arrcolumnorder(index), lookin:=xlvalues, lookat:=xlwhole, _        searchorder:=xlbycolumns, searchdirection:=xlnext, matchcase:=false) 

should have been "... find (arrcolumnorder(index, 1)..."

the answer why button moving did not set format option of button (right click button, format control>properties>select "don't move or size cells.") when things moving (and weirdly because code wrong) button moved cell when column copied , pasted.

here final code , works , expected do. namely, creates array data in range "xfd1:xfd95" (on separate worksheet have column headers stored in proper order), sorts columns in active worksheet match order of array. did not want explicitly call sheet name run on varying sheets. using find opposed match, works fine me not huge amount of data i'm dealing speed not issue.

sub reorder_columns()     dim arrcolumnorder variant     dim index interger     dim found range     dim tick integer      arrcolumnorder = userformdropdowndatasheet.range("xfd1:xfd95").value     application.screenupdating = false     tick = 1         index = lbound(arrcolumnorder) ubound(arrcolumnorder)            set found = rows("1:1").find(arrcolumnorder(index, 1), lookin:=xlvalues, lookat:=xlwhole, _            searchorder:=xlbycolumns, searchdirection:=xlnext, matchcase:=false)                if not found nothing                    if found.column <> tick                    found.entirecolumn.cut                    columns(tick).insert shift:=xltoright                    application.cutcopymode = false                    end if                tick = tick + 1                end if         next index     application.screenupdating = true end sub 

for me, 1 of big lessons here not try write code when have slept 2 hours! tired , making silly mistakes because not thinking clearly. got night's rest , morning able see went wrong.