Hi,
I have a long, complex formula in Excel which works fine. However when I recorded a macro in VBA it came out all wrong.
The formula is
=IF(RIGHT(A2,3)="000",IF(B2="R",NETWORKDAYS(C2,H2,hols),NETWORKDAYS(C2,TODAY(),hols)),IF(B2="r",(IF(F2<>"-",NETWORKDAYS(F2,H2,hols),IF(E2<>"-",NETWORKDAYS(E2,H2,hols),IF(D2<>"-",NETWORKDAYS(D2,H2,hols),"")))),NETWORKDAYS(D2,TODAY(),hols)))
In VBA this comes out as
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-9],3)=""000"" IF(RC[-8]=""R"",NETWORKDAYS(RC[-7],RC[-2],hols),NETWORKDAYS(RC[-7],TODAY(),hols))?RC[-8]=""r"" RC[-4]<>""-"" NETWORKDAYS(RC[-4],RC[-2],hols)?RC[-5]<>""-"" NETWORKDAYS(RC[-5],RC[-2],hols)?RC[-6]<>""-"" NETWORKDAYS RC[-6] "
Any ideas how I can manually hack VBA to do what Excel does?
Jude
I have a long, complex formula in Excel which works fine. However when I recorded a macro in VBA it came out all wrong.
The formula is
=IF(RIGHT(A2,3)="000",IF(B2="R",NETWORKDAYS(C2,H2,hols),NETWORKDAYS(C2,TODAY(),hols)),IF(B2="r",(IF(F2<>"-",NETWORKDAYS(F2,H2,hols),IF(E2<>"-",NETWORKDAYS(E2,H2,hols),IF(D2<>"-",NETWORKDAYS(D2,H2,hols),"")))),NETWORKDAYS(D2,TODAY(),hols)))
In VBA this comes out as
ActiveCell.FormulaR1C1 = _
"=RIGHT(RC[-9],3)=""000"" IF(RC[-8]=""R"",NETWORKDAYS(RC[-7],RC[-2],hols),NETWORKDAYS(RC[-7],TODAY(),hols))?RC[-8]=""r"" RC[-4]<>""-"" NETWORKDAYS(RC[-4],RC[-2],hols)?RC[-5]<>""-"" NETWORKDAYS(RC[-5],RC[-2],hols)?RC[-6]<>""-"" NETWORKDAYS RC[-6] "
Any ideas how I can manually hack VBA to do what Excel does?
Jude