excel - Using VBA to evaluate the formula and add the results to a range -

excel - Using VBA to evaluate the formula and add the results to a range -

i've written macro prepare files , include adding below formula each row part of automation. formula same each row problem formula long.

=upper(concatenate(if(j2=0,"",alphanumericonly(substitute(j2,"0",""))),if(m2=0,"",len(m2)),if(n2=0,"",upper(alphanumericonly(substitute(n2,"0","")))),if(p2=0,"",upper(alphanumericonly(substitute(p2,"0","")))),if(r2=0,"",upper(alphanumericonly(substitute(r2,"0","")))),if(w2=0,"",upper(alphanumericonly(substitute(w2,"0","")))),if(x2=0,"",upper(alphanumericonly(substitute(x2,"0","")))),if(y2=0,"",upper(alphanumericonly(substitute(y2,"0","")))),if(z2=0,"",substitute(substitute(substitute(z2,"-","1"),".",""),"0","")),if(ad2=0,"",substitute(substitute(substitute(ad2,"-","1"),".",""),"0",""))))

i have tried recording formula beingness added help, result has parts of macro deleted. if long.

could provide method add together formula each row? have read can evaluate formula result in vba , homecoming results, rather adding formula sheet. should looking @ route?

at top of module sheet set declaration (under option explicit if there).

public const csformula = "=upper(concatenate(if(j2=0,"""",alphanumericonly(substitute(j2,""0"",""""))),if(m2=0,"""",len(m2)),if(n2=0,"""",upper(alphanumericonly(substitute(n2,""0"","""")))),if(p2=0,"""",upper(alphanumericonly(substitute(p2,""0"","""")))),if(r2=0,"""",upper(alphanumericonly(substitute(r2,""0"","""")))),if(w2=0,"""",upper(alphanumericonly(substitute(w2,""0"","""")))),if(x2=0,"""",upper(alphanumericonly(substitute(x2,""0"","""")))),if(y2=0,"""",upper(alphanumericonly(substitute(y2,""0"","""")))),if(z2=0,"""",substitute(substitute(substitute(z2,""-"",""1""),""."",""""),""0"","""")),if(ad2=0,"""",substitute(substitute(substitute(ad2,""-"",""1""),""."",""""),""0"",""""))))"

now can fill formula range. example:

range("l2:l10").formula = csformula 'range("l2:l10") = range("l2:l10").value

the sec commented line in case want revert values teh inserted formula. note i'm using .formula , not .formular1c1.

excel vba excel-vba