i've been trying find error in excel formula when enter in single cell works , if copy other cells works, when record macro , run it fails , shows error saying there's syntax error in formula. here's code , how looks in excel:
sub macrotest() ' ' macrotest macro ' ' range("j2").select activecell.formular1c1 = _ "=if(isna(if(rc[-8]<>r[-1]c[-8],if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""jul"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],mat"& _ """,sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""aug"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""aug"","& _ "-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""sep"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""sep"",sheet3!c[-9]"& _ "eft(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""oct"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""oct"",sheet3!c[-9],0)),if(left"& _ "eet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""nov"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""nov"",sheet3!c[-9],0)),if(left(index(sheet"& _ "tch(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""dec"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""dec"",sheet3!c[-9],0)),"" "")))))),"" "")), "" "", if("& _ "[-1]c[-8],if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""jul"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""jul"",sheet3!c[-"& _ "(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""aug"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""aug"",sheet3!c[-9],0)),if(le"& _ "sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""sep"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""sep"",sheet3!c[-9],0)),if(left(index(she"& _ "match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""oct"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""oct"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],mat"& _ "rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""nov"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""nov"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc["& _ "c[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0)-1),3)=""dec"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&"" total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""dec"",sheet3!c[-9],0)),"" "")))))),"" ""))""& _ " range("j2").select selection.autofill destination:=range("j2:j117") range("j2:j117").select activewindow.smallscroll down:=-18 range("a1").select end sub excel:
=if(isna(if(b2<>b1,if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="jul",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("jul",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="aug",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("aug",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="sep",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("sep",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="oct",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("oct",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="nov",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("nov",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="dec",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("dec",sheet3!a:a,0))," "))))))," ")), " ", if(b2<>b1,if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="jul",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("jul",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="aug",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("aug",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="sep",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("sep",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="oct",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("oct",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="nov",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("nov",sheet3!a:a,0)),if(left(index(sheet1!o:o,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0)-1),3)="dec",index(sheet1!p:p,match(right(b2,len(b2)-search(" ",b2))&","&" "&left(b2,search(" ",b2)-1)&" total",sheet1!g:g,0))/index(sheet3!b:b,match("dec",sheet3!a:a,0))," "))))))," "))
i started excel formula , used text editor convert to
activecell.formular1c1 = _ "=if(isna(if(rc[-8]<>r[-1]c[-8],if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""jul"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""jul"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""aug"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""aug"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""sep"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""sep"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""oct"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""oct"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""nov"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""nov"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""dec"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""dec"",sheet3!c[-9],0)),"" "")))))),"" "")), "" "", if(rc[-8]<>r[-1]c[-8],if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""jul"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""jul"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""aug"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""aug"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""sep"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""sep"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""oct"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""oct"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""nov"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""nov"",sheet3!c[-9],0)),if(left(index(sheet1!c[5],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&""total"",sheet1!c[-3],0)-1),3)=""dec"",index(sheet1!c[6],match(right(rc[-8],len(rc[-8])-search("" "",rc[-8]))&"",""&"" ""&left(rc[-8],search("" "",rc[-8])-1)&" & _ """total"",sheet1!c[-3],0))/index(sheet3!c[-8],match(""dec"",sheet3!c[-9],0)),"" "")))))),"" ""))" after executing in cell j2, compared formula produced excel formula again , verified says same thing.
Comments
Post a Comment