firstly, long , ugly formula warning.
what trying vba insert formula excel. if vba insert without '=' , manually add in excel, proves formula works. yet if try vba insert , run formula, '=' prefix, in 1 go run-time error.
any ideas how can vba insert , run formula?
activecell.formular1c1 = _ "=if(p1=""eqs fw"",if(statistics!b3=""cadmium"",if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$5,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$5),'hardness-bands'!$d$5,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$6,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$6),'hardness-bands'!$d$6,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$7,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$7),'hardness-bands'!$d$7,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$8,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))" & _ "<'hardness-bands'!$c$8),'hardness-bands'!$d$8,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$9,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$9),'hardness-bands'!$d$9,""err""))))),if(statistics!b4=""copper"",if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$13,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$13),'hardness-bands'!$d$13,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$14,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$14),'hardness-bands'!$d$14,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$15,indirect(address(row(index($b$3:$b$300," & _ "match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$15),'hardness-bands'!$d$15,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$16,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$16),'hardness-bands'!$d$16,""err"")))),if(statistics!b4=""zinc"",if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$20,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$20),'hardness-bands'!$d$20,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$21,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$21),'hardness-bands'!$d$21,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-ban" & _ "ds'!$b$22,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$22),'hardness-bands'!$d$22,if(and(indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))>'hardness-bands'!$b$23,indirect(address(row(index($b$3:$b$300,match(""alkalinity caco3"",$b$3:$b$300,0))),9))<'hardness-bands'!$c$23),'hardness-bands'!$d$23,""err"")))),statistics!r4))),statistics!r3)"
wow, pretty big. ought break fair amount.
anyway, think might because trying insert speech marks string. in vba debug mode, copy entire string , have @ in immediate window. same string when in excel cell? guess not.
try replacing double speech marks: p1=""eqs fw""
with this: p1=" & """" & "eqs fw" & """"
that insert speech marks correctly you.
another thought. tricky doing speech mark adjustments, start trying out on small section of vast formula , build gradually.
Comments
Post a Comment