Small Logical Problem In VBA code

zaska

Well-known Member
Joined
Oct 24, 2010
Messages
1,046
Hi,

Sample Data

<table border="0" cellpadding="0" cellspacing="0" width="1074"><col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <col style="width:48pt" span="6" width="64"> <col style="mso-width-source:userset;mso-width-alt:3364;width:69pt" width="92"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> <col style="mso-width-source:userset;mso-width-alt:3437;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:3547;width:73pt" width="97"> <col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:3730;width:77pt" width="102"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:77pt;font-size:11.0pt; color:white;font-weight:700;text-decoration:none;text-underline-style:none; text-line-through:none;font-family:Calibri;border-top:.5pt solid #95B3D7; border-right:none;border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7; background:#4F81BD;mso-pattern:#4F81BD none" height="20" width="102">SYMBOL</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">SERIES</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">OPEN</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">HIGH</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">LOW</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">CLOSE</td> <td style="width:48pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="64">LAST</td> <td style="width:69pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="92">PREVCLOSE</td> <td style="width:71pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="95">TOTTRDQTY</td> <td style="width:71pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="94">TOTTRDVAL</td> <td style="width:73pt;font-size:11.0pt;color:white;font-weight:700; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="97">TIMESTAMP</td> <td style="width:81pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="108">TOTALTRADES</td> <td style="width:77pt;font-size:11.0pt;color:white;font-weight: 700;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7; border-bottom:.5pt solid #95B3D7;border-left:none;background:#4F81BD; mso-pattern:#4F81BD none" width="102">ISIN</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">20MICRONS</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">55</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">55.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">53.55</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">53.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">53.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">53.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">51260</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2784857.3</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">319</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE144J01019</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">3IINFOTECH</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">27.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">27.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">25.85</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26.15</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">711199</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">18666482.75</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">3922</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE748C01020</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">3MINDIA</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4190</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4250.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4110</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4215.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4186.1</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4203.75</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">673</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">2801103</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">192</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE470A01017</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">ANANTRAJ</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">58</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">60.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">57.35</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">58.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">58.85</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">59.15</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">84545</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">4939166.3</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">1172</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE242C01024</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">ANDHRABANK</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">121</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">121.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">117.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">119.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">118.45</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">121.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">419235</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">49737025.45</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">4342</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE434A01013</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">ANDHRACEMT</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">BE</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">10.15</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">10.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">9.85</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">10</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">10</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">10.35</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">9406</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">93654.1</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">47</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE666E01012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">ANDHRSUGAR</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">98.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">99.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">97.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">97.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">97.55</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">98.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">11293</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">1107342.55</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">157</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE715B01013</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">ANGIND</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">22.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">22.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">20.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">253</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">5435.6</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">9</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE017D01010</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">ANIKINDS</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">34</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">34</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">32.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">32.9</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">32.7</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">34.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">633</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">20973.45</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">50</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE087B01017</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">ANKURDRUGS</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ
</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">32.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">32.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">30.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">30.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">30.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">32.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">17450</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">537891.8</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">101</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE238D01012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">ANKURDRUGS</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">BE</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">32.4</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">33</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">34</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">35</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">36</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">37</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">1652</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">5555562</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">101</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE238D01012</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">ANSALHSG</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">38</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">39</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">37.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">38.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">38.1</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">38.6</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">2265</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">86625.6</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">88</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE880B01015</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">ANTGRAPHIC</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">BE</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">0.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">50226</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">10705.25</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">16</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE414B01021</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">APARINDS</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">169.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">169.95</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">164.1</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">166</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">166</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">167.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">6011</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">998169</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">77</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE372A01015</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7;background: #DCE6F1;mso-pattern:#DCE6F1 none" height="20">APCOTEXIND</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">175.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">175.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">161.2</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">168.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">168.5</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">166.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">335</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">55780.3</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none;background:#DCE6F1; mso-pattern:#DCE6F1 none" align="right">145</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none;background:#DCE6F1;mso-pattern:#DCE6F1 none">INE116A01016</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt;font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:.5pt solid #95B3D7" height="20">APIL</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:none;border-bottom:.5pt solid #95B3D7; border-left:none">EQ</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">522.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">522.05</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">505.25</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">516.3</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">512</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">519.8</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">43680</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">22430826.85</td> <td class="xl63" style="font-size:11.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">26-Sep-11</td> <td style="font-size:11.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:Calibri;border-top:.5pt solid #95B3D7;border-right:none; border-bottom:.5pt solid #95B3D7;border-left:none" align="right">3173</td> <td style="font-size:11.0pt;color:black;font-weight:400;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Calibri; border-top:.5pt solid #95B3D7;border-right:.5pt solid #95B3D7;border-bottom: .5pt solid #95B3D7;border-left:none">INE878A01011</td> </tr> </tbody></table>
Code :
Code:
    Dim i As Long, LR As Long, LastRow As Long
    startPath = "E:\Macros\Input\" 'current directory
    Application.ScreenUpdating = False
    ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\cm" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv"
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    With Range("A1:M" & LastRow)
     .AutoFilter Field:=2, Criteria1:="<>BE", Operator:=xlAnd, Criteria2:="<>EQ"
     .Offset(1, 0).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    Range("B" & i).NumberFormat = "yyyymmdd"
    Range("B" & i).Value = Range("K" & i).Value
    Next i
    Range("G:H, J:M").Delete
    Rows("1:1").Delete
    Workbooks.OpenText Filename:="E:\Macros\Input\MTO_" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmyyyy") & ".DAT", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
    Rows("1:3").Delete
    Range("A:B ,E:E,G:G ").Delete
     With Range("A1:C" & LastRow)
     .AutoFilter Field:=2, Criteria1:="<>BE", Operator:=xlAnd, Criteria2:="<>EQ"
     .Offset(1, 0).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    Windows("cm" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv").Activate
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("H1:H" & LastRow).Formula = "=IF(COUNTIF(INDIRECT(""'MTO_""&TEXT('[NSE Converter.xls]Sheet1'!$G$2,""ddmmyyyy"")&"".DAT'!$A:$A""),A1),VLOOKUP(A1,INDIRECT(""'MTO_""&TEXT('[NSE Converter.xls]Sheet1'!$G$2,""ddmmyyyy"")&"".DAT'!$A:$C""),3,0),0)"
      ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\cm" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV _
        , CreateBackup:=False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
    ActiveWindow.Close
    ActiveWindow.Close
    Application.ScreenUpdating = False

I am facing a problem when Same Symbol is Present both in "EQ" & "BE" series the code is taking both symbols in the output. Ex: ANKURDRUGS in the sample data

Can anyone suggest as to what changes should be made in the above code so that i can eliminate "BE" series and take the data of only "EQ" Series of that particular symbol only when a symbol is present both in "EQ" & "BE" Series.

Thank you
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Maybe something like this

Code:
'Tests for same symbol + ('EQ' over 'BE'); Deletes 'BE' if found
    For i = 2 To LR
 
    If _ 
    (Range("A" & i) =   Range("A" & i-1)) And _
    (Range("B" & i) = "BE"And Range("B" & i-1) = "EQ") then 
     Range("B" & i).EntireRow.Delete 
    i=i-1 'maybe, as to not skip rows
    End If
    Next i
 
Last edited:
Upvote 0
I have two more queries with the same code

1. How to add loop structure so that i can process the data of more than 1 file without having to change the date in the Imput File.

2. Is there any way to tweak or reduce the code further.(just asking for knowledge purpose)

Kindly give your valuable inputs.



Code:
    Dim i As Long, LR As Long, LastRow As Long
    startPath = "E:\Macros\Input\" 'current directory
    Application.ScreenUpdating = False
    ChDir "E:\Macros\Input"
    Workbooks.Open Filename:="E:\Macros\Input\cm" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv"
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    With Range("A1:M" & LastRow)
     .AutoFilter Field:=2, Criteria1:="<>BE", Operator:=xlAnd, Criteria2:="<>EQ"
     .Offset(1, 0).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    LR = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
    If (Range("A" & i) = Range("A" & i - 1)) And (Range("B" & i) = "BE" And Range("B" & i - 1) = "EQ") Then
        Range("B" & i).EntireRow.Delete
    i = i - 1 'maybe, as to not skip rows
    End If
    Next i
    For i = 2 To LR
    Range("B" & i).NumberFormat = "yyyymmdd"
    Range("B" & i).Value = Range("K" & i).Value
    Next i
    Range("G:H, J:M").Delete
    Rows("1:1").Delete
    Workbooks.OpenText Filename:="E:\Macros\Input\MTO_" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmyyyy") & ".DAT", Origin:=437, _
        StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), TrailingMinusNumbers:=True
    Rows("1:3").Delete
    Range("A:B ,E:E,G:G ").Delete
     With Range("A1:C" & LastRow)
     .AutoFilter Field:=2, Criteria1:="<>BE", Operator:=xlAnd, Criteria2:="<>EQ"
     .Offset(1, 0).EntireRow.Delete
    End With
    ActiveSheet.AutoFilterMode = False
    Windows("cm" & Format(Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value, "ddmmmyyyy") & "bhav.csv").Activate
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    Range("H1:H" & LastRow).Formula = "=IF(COUNTIF(INDIRECT(""'MTO_""&TEXT('[NSE Converter.xls]Sheet1'!$G$2,""ddmmyyyy"")&"".DAT'!$A:$A""),A1),VLOOKUP(A1,INDIRECT(""'MTO_""&TEXT('[NSE Converter.xls]Sheet1'!$G$2,""ddmmyyyy"")&"".DAT'!$A:$C""),3,0),0)"
      ActiveWorkbook.SaveAs Filename:="E:\Macros\Output\Stocks\cm" & Format(Date, "ddmmmyyyy") & "bhav.txt", FileFormat:=xlCSV _
        , CreateBackup:=False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = True
    ActiveWindow.Close
    ActiveWindow.Close
    Application.ScreenUpdating = False

Thank you
 
Upvote 0
What does this date represent?
Workbooks("NSE Converter.xls").Sheets("Sheet1").Range("G2").Value
Is that what is updated on the 'Imput File'?

Is this something that runs daily?
Are you processing all files in from and to the same directories?
 
Upvote 0
Yes this code runs daily and the Formula for the Date in G2 is =Today().
If i miss any days data ( due to internet problem etc..,) i change the date manually to process the files.

Actually the files are in .zip format and i manually upzip them which are .csv files in the following format

cm26SEP2011bhav.csv for TOmorrow the sourcefile would Change to cm27SEP2011bhav.csv
MTO_26092011.DAT and MTO_27092011.DAT

If i don't have internet connection for 1Month then this is really a hectic task for me.

The source files are downloaded to Input Folder and the Processed Files are stored in folder named Output.

Is it possible to change the code so that i could choose between two dates for the files to process?

and Moreover there will be no data for Saturdays and Sundays.

Thank you
 
Upvote 0
Yes this code runs daily and the Formula for the Date in G2 is =Today().
If i miss any days data ( due to internet problem etc..,) i change the date manually to process the files.

Actually the files are in .zip format and i manually upzip them which are .csv files in the following format

cm26SEP2011bhav.csv for TOmorrow the sourcefile would Change to cm27SEP2011bhav.csv
MTO_26092011.DAT and MTO_27092011.DAT

If i don't have internet connection for 1Month then this is really a hectic task for me.

The source files are downloaded to Input Folder and the Processed Files are stored in folder named Output.

Is it possible to change the code so that i could choose between two dates for the files to process?

and Moreover there will be no data for Saturdays and Sundays.

Thank you
bump
 
Upvote 0
Could do something like this.....
Prompt for start date + end date
Loop through them, testing if weekend, then skip date, else build file names to open/process...

Code:
Sub DateLoop()
Const basein = "E:\Macros\Input\"
Const baseout = "E:\Macros\Output\Stocks\"
StartDt = Application.InputBox(Prompt:="Start Date:", _
    Default:=Format(Date, "mm/dd/yyyy"), _
    Title:="", _
    Type:=1)
EndDt = Application.InputBox(Prompt:="End Date:", _
    Default:=Format(Date, "mm/dd/yyyy"), _
    Title:="", _
    Type:=1)
For currDt = StartDt To EndDt
 
    If Weekday(currDt, vbMonday) < 6 Then
 
    csvfile = "cm" & Format(currDt, "ddmmmyyyy") & "bhav.csv"
    datfile = "MTO_" & Format(currDt, "ddmmyyyy") & ".DAT" 'MTO_26092011.DAT
    txtfile = "cm" & Format(currDt, "ddmmmyyyy") & "bhav.txt"
 
        Debug.Print csvfile, datfile, txtfile
    Else
        Debug.Print
    End If
Next currDt
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,507
Messages
6,179,172
Members
452,893
Latest member
denay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top