PDA

View Full Version : Solved: long function from excel to vba



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

snb
02-13-2013, 05:26 AM
I think there's another problem:
This suffices to enter a formula:

ThisWorkbook.Worksheets(shName).Range("A1:A100")= "=....."

white_flag
02-13-2013, 05:41 AM
Hi snb

thx for that but in my case:


ThisWorkbook.Worksheets(shName).Range("A1:A100") = "=INDEX(INDIRECT(" '"&B7&"'!A26:A62")"
error because I have this '

snb
02-13-2013, 06:14 AM
There's more wrong than only hyphens in the formula:


Sub tst()
x3 = Replace("=INDEX(INDIRECT(~'~&$B$7&~'!A26:A62~),1,1)", "~", Chr(34))
Range("A1:A100") = x3
End Sub

white_flag
02-13-2013, 06:35 AM
normally this formula is from excel and it s working


=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))))


putted like you told me:


Sub tst()
x3 = Replace("=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))))", "~", Chr(34))
Range("A7:A15") = x3
End Sub


error 91

mohanvijay
02-13-2013, 07:09 AM
you have to enter two double quotes like below


"=IF(B7=0;0;(IF(C7<114;INDEX(INDIRECT(""'""&B7&""'!A26:A62"");IF(ISNA(MATCH(C7+D7;INDIRECT(""'""&B7&""'!A26"

snb
02-13-2013, 07:11 AM
Please post a sample workbook.

white_flag
02-13-2013, 07:25 AM
double quotes did the trick.
thx mohanvijay and snb for your time