array not working as a macro

Hi i hope someone can help i have a spread sheet which up dates from information stored in a data folder. I have tried to add a formula to the spread sheet (a formula using an array) and then update the macro by starting macro , F2 on cell formula is in , stopping macro, then coping the formula from the modual into the main maco sheet, but it does not work

here is the actual formula in the spread sheet which works but when i go to another item from the drop down it doeas not.


{=if(ISNUMBER(MATCH(TRUE,Q7:$Q$534,0)),IF(INDEX(S7 :$S$534,MATCH(TRUE,Q7:$Q$534,0))=S6,"",IF(S6="Long ",INDEX($R5:$R$6,MATCH(2,1/($S5:$S$6="short")))-R6,IF(S6="Short",R6-INDEX($R5:$R$6,MATCH(2,1/($S5:$S$6="Long"))),""))),"")}

this is it once i have copied from macro
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 13-06-2007 by paulm
'

'
Selection.FormulaArray = _
"=ISNUMBER(MATCH(TRUE,R[1]C[-7]:R534C17,0))?INDEX(R[1]C[-5]:R534C19,MATCH(TRUE,R[1]C[-7]:R534C17,0))=RC[-5] """" RC[-5]=""Long"" INDEX(R[-1]C18:R6C18,MATCH(2,1/(R[-1]C19:R6C19=""short"")))-RC[-6] RC[-5]=""Short"" RC[-6] R[-1]C18:R6C18 2 1/(R[-1]C19:R6C19=""Long"") "
End Sub

any ideas would be welcolme.
I must thank NBVC for his help in this and antoka05 who have provided great help.