white_flag
02-13-2013, 04:27 AM
Hello,
I like to insert this function via VBA:
ThisWorkbook.Worksheets(shName).Range("A1:A100").Address).Formula
"=IF(B7=0;0;(IF(C7<114;INDEX(INDIRECT("'"&B7&"'!A26:A62");IF(ISNA(MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62")));1;IF(ISNA(MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62");0));MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62"))+1;MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62");0)));1);INDEX(INDIRECT("'"&B7&"'!A26:A62");IF(ISNA(MATCH(C7;INDIRECT("'"&B7&"'!A26:A62")));1;IF(ISNA(MATCH(C7;INDIRECT("'"&B7&"'!A26:A62");0));MATCH(C7;INDIRECT("'"&B7&"'!A26:A62"))+1;MATCH(C7;INDIRECT("'"&B7&"'!A26:A62");0)));1))))"
but on indirect function I have ' comment character and vba think that is a comment
can someone help me to put this function correct (or a better solution).
thank you
I like to insert this function via VBA:
ThisWorkbook.Worksheets(shName).Range("A1:A100").Address).Formula
"=IF(B7=0;0;(IF(C7<114;INDEX(INDIRECT("'"&B7&"'!A26:A62");IF(ISNA(MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62")));1;IF(ISNA(MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62");0));MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62"))+1;MATCH(C7+D7;INDIRECT("'"&B7&"'!A26:A62");0)));1);INDEX(INDIRECT("'"&B7&"'!A26:A62");IF(ISNA(MATCH(C7;INDIRECT("'"&B7&"'!A26:A62")));1;IF(ISNA(MATCH(C7;INDIRECT("'"&B7&"'!A26:A62");0));MATCH(C7;INDIRECT("'"&B7&"'!A26:A62"))+1;MATCH(C7;INDIRECT("'"&B7&"'!A26:A62");0)));1))))"
but on indirect function I have ' comment character and vba think that is a comment
can someone help me to put this function correct (or a better solution).
thank you