Todd Bardoni
Well-known Member
- Joined
- Aug 29, 2002
- Messages
- 3,042
I wrote a rather large formula that works in Excel but, when I tried to record it in VB it said it couldn't record. So, I copied it, went into VB and Pasted it. But then I got a compile error. The formula is 562 characters long. Here's the formula:
=IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)<10),"02-"&"00"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)<100,DCOUNT(BE:BE,1,BE1:BE5)>=10),"02-"&"0"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)>99),"02-"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)<10),"02-"&"00"&DCOUNT(BF:BF,1,BF1:BF5)&"C",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)<100,DCOUNT(BF:BF,1,BF1:BF5)>=10),"02-"&"0"&DCOUNT(BF:BF,1,BF1:BF5)&"C",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)>99),"02-"&DCOUNT(BF:BF,1,BF1:BF5)&"C",""))))))
=IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)<10),"02-"&"00"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)<100,DCOUNT(BE:BE,1,BE1:BE5)>=10),"02-"&"0"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="New",DCOUNT(BE:BE,1,BE1:BE5)>99),"02-"&DCOUNT(BE:BE,1,BE1:BE5)&"N",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)<10),"02-"&"00"&DCOUNT(BF:BF,1,BF1:BF5)&"C",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)<100,DCOUNT(BF:BF,1,BF1:BF5)>=10),"02-"&"0"&DCOUNT(BF:BF,1,BF1:BF5)&"C",IF(AND(C2="Change",DCOUNT(BF:BF,1,BF1:BF5)>99),"02-"&DCOUNT(BF:BF,1,BF1:BF5)&"C",""))))))