Figuring out optimal schedules

S

Sal Paradise

Well-known Member
Joined
Oct 23, 2006
Messages
2,457
I have a list of dates and what each hour would get in revenue as follows:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;text-align: center;;"></td><td style="text-align: right;;">0</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">17</td><td style="text-align: right;;">18</td><td style="text-align: right;;">19</td><td style="text-align: right;;">20</td><td style="text-align: right;;">21</td><td style="text-align: right;;">22</td><td style="text-align: right;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2007/1/1</td><td style="text-align: right;;">-$21.99</td><td style="text-align: right;;">-$22.28</td><td style="text-align: right;;">-$24.20</td><td style="text-align: right;;">-$25.20</td><td style="text-align: right;;">-$25.13</td><td style="text-align: right;;">-$24.16</td><td style="text-align: right;;">-$22.51</td><td style="text-align: right;;">-$21.76</td><td style="text-align: right;;">-$21.23</td><td style="text-align: right;;">-$20.42</td><td style="text-align: right;;">-$19.67</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.39</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.75</td><td style="text-align: right;;">-$19.87</td><td style="text-align: right;;">-$19.47</td><td style="text-align: right;;">-$14.58</td><td style="text-align: right;;">-$13.31</td><td style="text-align: right;;">-$14.60</td><td style="text-align: right;;">-$15.81</td><td style="text-align: right;;">-$16.93</td><td style="text-align: right;;">-$18.58</td><td style="text-align: right;;">-$19.82</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2007/1/2</td><td style="text-align: right;;">-$18.66</td><td style="text-align: right;;">-$19.19</td><td style="text-align: right;;">-$19.63</td><td style="text-align: right;;">-$19.73</td><td style="text-align: right;;">-$19.44</td><td style="text-align: right;;">-$17.93</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$9.03</td><td style="text-align: right;;">-$7.68</td><td style="text-align: right;;">-$5.25</td><td style="text-align: right;;">-$3.34</td><td style="text-align: right;;">-$6.83</td><td style="text-align: right;;">-$9.50</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$14.02</td><td style="text-align: right;;">-$14.67</td><td style="text-align: right;;">-$14.30</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$26.92</td><td style="text-align: right;;">$12.56</td><td style="text-align: right;;">$7.12</td><td style="text-align: right;;">-$1.56</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$14.02</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2007/1/3</td><td style="text-align: right;;">-$15.79</td><td style="text-align: right;;">-$16.54</td><td style="text-align: right;;">-$16.91</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$16.84</td><td style="text-align: right;;">-$14.69</td><td style="text-align: right;;">-$11.63</td><td style="text-align: right;;">-$2.76</td><td style="text-align: right;;">-$2.10</td><td style="text-align: right;;">-$5.39</td><td style="text-align: right;;">-$5.19</td><td style="text-align: right;;">-$9.40</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$13.54</td><td style="text-align: right;;">-$14.53</td><td style="text-align: right;;">-$14.96</td><td style="text-align: right;;">-$14.72</td><td style="text-align: right;;">$2.16</td><td style="text-align: right;;">$12.48</td><td style="text-align: right;;">$1.56</td><td style="text-align: right;;">-$5.17</td><td style="text-align: right;;">-$7.09</td><td style="text-align: right;;">-$14.55</td><td style="text-align: right;;">-$16.03</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2007/1/4</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$18.43</td><td style="text-align: right;;">-$18.91</td><td style="text-align: right;;">-$19.07</td><td style="text-align: right;;">-$18.61</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$13.01</td><td style="text-align: right;;">-$4.21</td><td style="text-align: right;;">-$4.59</td><td style="text-align: right;;">-$8.14</td><td style="text-align: right;;">-$8.56</td><td style="text-align: right;;">-$10.71</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$14.14</td><td style="text-align: right;;">-$14.91</td><td style="text-align: right;;">-$15.39</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$1.65</td><td style="text-align: right;;">$10.45</td><td style="text-align: right;;">-$0.57</td><td style="text-align: right;;">-$6.95</td><td style="text-align: right;;">-$10.58</td><td style="text-align: right;;">-$16.11</td><td style="text-align: right;;">-$16.74</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2007/1/5</td><td style="text-align: right;;">-$18.15</td><td style="text-align: right;;">-$18.92</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.42</td><td style="text-align: right;;">-$18.27</td><td style="text-align: right;;">-$14.38</td><td style="text-align: right;;">-$7.80</td><td style="text-align: right;;">-$8.06</td><td style="text-align: right;;">-$10.27</td><td style="text-align: right;;">-$10.33</td><td style="text-align: right;;">-$10.97</td><td style="text-align: right;;">-$12.89</td><td style="text-align: right;;">-$13.48</td><td style="text-align: right;;">-$14.19</td><td style="text-align: right;;">-$14.63</td><td style="text-align: right;;">-$14.43</td><td style="text-align: right;;">-$7.40</td><td style="text-align: right;;">-$1.32</td><td style="text-align: right;;">-$6.14</td><td style="text-align: right;;">-$10.10</td><td style="text-align: right;;">-$13.70</td><td style="text-align: right;;">-$16.17</td><td style="text-align: right;;">-$17.03</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2007/1/6</td><td style="text-align: right;;">-$18.85</td><td style="text-align: right;;">-$19.16</td><td style="text-align: right;;">-$19.71</td><td style="text-align: right;;">-$20.41</td><td style="text-align: right;;">-$20.59</td><td style="text-align: right;;">-$20.11</td><td style="text-align: right;;">-$19.03</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$13.64</td><td style="text-align: right;;">-$12.69</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$13.56</td><td style="text-align: right;;">-$13.89</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$15.78</td><td style="text-align: right;;">-$16.37</td><td style="text-align: right;;">-$15.41</td><td style="text-align: right;;">-$6.45</td><td style="text-align: right;;">-$4.93</td><td style="text-align: right;;">-$8.22</td><td style="text-align: right;;">-$12.29</td><td style="text-align: right;;">-$13.40</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$18.10</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2007/1/7</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$19.20</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.23</td><td style="text-align: right;;">-$17.99</td><td style="text-align: right;;">-$16.39</td><td style="text-align: right;;">-$14.54</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$13.98</td><td style="text-align: right;;">-$13.95</td><td style="text-align: right;;">-$14.46</td><td style="text-align: right;;">-$14.92</td><td style="text-align: right;;">-$15.36</td><td style="text-align: right;;">-$15.91</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$1.58</td><td style="text-align: right;;">$5.88</td><td style="text-align: right;;">$4.58</td><td style="text-align: right;;">-$3.12</td><td style="text-align: right;;">-$9.62</td><td style="text-align: right;;">-$13.90</td><td style="text-align: right;;">-$18.85</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">2007/1/8</td><td style="text-align: right;;">-$18.50</td><td style="text-align: right;;">-$19.49</td><td style="text-align: right;;">-$19.94</td><td style="text-align: right;;">-$20.04</td><td style="text-align: right;;">-$19.52</td><td style="text-align: right;;">-$17.01</td><td style="text-align: right;;">-$10.56</td><td style="text-align: right;;">$6.69</td><td style="text-align: right;;">$6.16</td><td style="text-align: right;;">$3.96</td><td style="text-align: right;;">$2.96</td><td style="text-align: right;;">$2.53</td><td style="text-align: right;;">$0.67</td><td style="text-align: right;;">-$5.20</td><td style="text-align: right;;">-$7.79</td><td style="text-align: right;;">-$9.83</td><td style="text-align: right;;">-$8.74</td><td style="text-align: right;;">$14.30</td><td style="text-align: right;;">$33.61</td><td style="text-align: right;;">$22.69</td><td style="text-align: right;;">$10.34</td><td style="text-align: right;;">$0.77</td><td style="text-align: right;;">-$11.88</td><td style="text-align: right;;">-$16.30</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">2007/1/10</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$16.30</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$16.28</td><td style="text-align: right;;">-$16.09</td><td style="text-align: right;;">-$13.42</td><td style="text-align: right;;">-$0.24</td><td style="text-align: right;;">$15.66</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$5.82</td><td style="text-align: right;;">$1.15</td><td style="text-align: right;;">-$2.61</td><td style="text-align: right;;">-$6.62</td><td style="text-align: right;;">-$11.36</td><td style="text-align: right;;">-$13.87</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$14.77</td><td style="text-align: right;;">$3.47</td><td style="text-align: right;;">$27.72</td><td style="text-align: right;;">$13.26</td><td style="text-align: right;;">$3.52</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;;">-$13.57</td><td style="text-align: right;;">-$12.00</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2007/1/11</td><td style="text-align: right;;">-$12.72</td><td style="text-align: right;;">-$12.70</td><td style="text-align: right;;">-$13.10</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$10.83</td><td style="text-align: right;;">$8.47</td><td style="text-align: right;;">$27.11</td><td style="text-align: right;;">$20.18</td><td style="text-align: right;;">$13.98</td><td style="text-align: right;;">$9.22</td><td style="text-align: right;;">$5.00</td><td style="text-align: right;;">$0.15</td><td style="text-align: right;;">-$3.11</td><td style="text-align: right;;">-$8.60</td><td style="text-align: right;;">-$10.75</td><td style="text-align: right;;">-$11.04</td><td style="text-align: right;;">$11.87</td><td style="text-align: right;;">$34.52</td><td style="text-align: right;;">$19.74</td><td style="text-align: right;;">$13.07</td><td style="text-align: right;;">$3.38</td><td style="text-align: right;;">-$9.55</td><td style="text-align: right;;">-$14.88</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2007/1/12</td><td style="text-align: right;;">-$14.42</td><td style="text-align: right;;">-$15.60</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$15.32</td><td style="text-align: right;;">-$15.30</td><td style="text-align: right;;">-$13.30</td><td style="text-align: right;;">$3.88</td><td style="text-align: right;;">$18.76</td><td style="text-align: right;;">$12.43</td><td style="text-align: right;;">$7.30</td><td style="text-align: right;;">$2.85</td><td style="text-align: right;;">-$1.37</td><td style="text-align: right;;">-$5.12</td><td style="text-align: right;;">-$8.15</td><td style="text-align: right;;">-$11.07</td><td style="text-align: right;;">-$12.53</td><td style="text-align: right;;">-$11.73</td><td style="text-align: right;;">$4.21</td><td style="text-align: right;;">$22.51</td><td style="text-align: right;;">$12.80</td><td style="text-align: right;;">$2.51</td><td style="text-align: right;;">-$2.92</td><td style="text-align: right;;">-$13.05</td><td style="text-align: right;;">-$14.36</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">2007/1/13</td><td style="text-align: right;;">-$16.78</td><td style="text-align: right;;">-$17.02</td><td style="text-align: right;;">-$18.30</td><td style="text-align: right;;">-$18.99</td><td style="text-align: right;;">-$18.96</td><td style="text-align: right;;">-$18.31</td><td style="text-align: right;;">-$16.45</td><td style="text-align: right;;">-$14.73</td><td style="text-align: right;;">-$10.54</td><td style="text-align: right;;">-$8.03</td><td style="text-align: right;;">-$7.87</td><td style="text-align: right;;">-$9.58</td><td style="text-align: right;;">-$11.28</td><td style="text-align: right;;">-$12.30</td><td style="text-align: right;;">-$13.92</td><td style="text-align: right;;">-$14.88</td><td style="text-align: right;;">-$13.85</td><td style="text-align: right;;">-$3.02</td><td style="text-align: right;;">$7.88</td><td style="text-align: right;;">-$3.06</td><td style="text-align: right;;">-$8.26</td><td style="text-align: right;;">-$10.53</td><td style="text-align: right;;">-$13.65</td><td style="text-align: right;;">-$15.98</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">2007/1/14</td><td style="text-align: right;;">-$16.94</td><td style="text-align: right;;">-$17.49</td><td style="text-align: right;;">-$18.60</td><td style="text-align: right;;">-$19.10</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.22</td><td style="text-align: right;;">-$18.65</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$16.21</td><td style="text-align: right;;">-$14.52</td><td style="text-align: right;;">-$14.08</td><td style="text-align: right;;">-$14.07</td><td style="text-align: right;;">-$14.93</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$15.51</td><td style="text-align: right;;">-$15.76</td><td style="text-align: right;;">-$14.00</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;;">$9.89</td><td style="text-align: right;;">$8.29</td><td style="text-align: right;;">$1.97</td><td style="text-align: right;;">-$6.55</td><td style="text-align: right;;">-$11.60</td><td style="text-align: right;;">-$15.96</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2007/1/15</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$17.00</td><td style="text-align: right;;">-$17.72</td><td style="text-align: right;;">-$18.02</td><td style="text-align: right;;">-$17.48</td><td style="text-align: right;;">-$16.31</td><td style="text-align: right;;">-$8.35</td><td style="text-align: right;;">-$1.10</td><td style="text-align: right;;">$1.98</td><td style="text-align: right;;">$6.29</td><td style="text-align: right;;">$8.09</td><td style="text-align: right;;">$6.47</td><td style="text-align: right;;">$1.39</td><td style="text-align: right;;">-$3.17</td><td style="text-align: right;;">-$7.37</td><td style="text-align: right;;">-$8.52</td><td style="text-align: right;;">-$7.04</td><td style="text-align: right;;">$12.81</td><td style="text-align: right;;">$39.27</td><td style="text-align: right;;">$26.56</td><td style="text-align: right;;">$13.34</td><td style="text-align: right;;">$5.88</td><td style="text-align: right;;">-$8.94</td><td style="text-align: right;;">-$10.94</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2007/1/16</td><td style="text-align: right;;">-$12.73</td><td style="text-align: right;;">-$14.40</td><td style="text-align: right;;">-$15.15</td><td style="text-align: right;;">-$15.24</td><td style="text-align: right;;">-$15.19</td><td style="text-align: right;;">-$11.16</td><td style="text-align: right;;">$5.19</td><td style="text-align: right;;">$22.62</td><td style="text-align: right;;">$21.09</td><td style="text-align: right;;">$17.01</td><td style="text-align: right;;">$18.36</td><td style="text-align: right;;">$16.65</td><td style="text-align: right;;">$11.58</td><td style="text-align: right;;">$5.02</td><td style="text-align: right;;">-$1.31</td><td style="text-align: right;;">-$3.89</td><td style="text-align: right;;">-$1.01</td><td style="text-align: right;;">$20.76</td><td style="text-align: right;;">$51.17</td><td style="text-align: right;;">$41.38</td><td style="text-align: right;;">$30.23</td><td style="text-align: right;;">$17.81</td><td style="text-align: right;;">-$1.28</td><td style="text-align: right;;">-$3.87</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">2007/1/17</td><td style="text-align: right;;">$10.65</td><td style="text-align: right;;">$0.30</td><td style="text-align: right;;">-$3.75</td><td style="text-align: right;;">-$3.76</td><td style="text-align: right;;">-$3.68</td><td style="text-align: right;;">$14.52</td><td style="text-align: right;;">$43.79</td><td style="text-align: right;;">$51.48</td><td style="text-align: right;;">$36.40</td><td style="text-align: right;;">$30.79</td><td style="text-align: right;;">$32.70</td><td style="text-align: right;;">$27.73</td><td style="text-align: right;;">$17.40</td><td style="text-align: right;;">$9.27</td><td style="text-align: right;;">$2.80</td><td style="text-align: right;;">$0.11</td><td style="text-align: right;;">$1.90</td><td style="text-align: right;;">$27.69</td><td style="text-align: right;;">$55.91</td><td style="text-align: right;;">$46.38</td><td style="text-align: right;;">$33.33</td><td style="text-align: right;;">$24.35</td><td style="text-align: right;;">$6.11</td><td style="text-align: right;;">$2.42</td></tr></tbody></table><p style="width:2.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Test</p><br /><br />

The operation, however, should be consecutive, so I can't just say "schedule for whenever the result is positive". What I would like to do is to write a formula that determines what the optimal schedule is given that constraint.

For instance, on January 10th, from 7-10 I am making a profit, but that profit is less than the loss I would hit from 11-16, so I shouldn't run from 7-10. Instead I should just run from 17-20, because that has a higher profit than the 7-10 chunk.

Is there an easy way to do this with formulas or VBA? Excel 2003 for reference.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
I already have them in red in the sheet (it just doesn't transfer), but that doesn't help me at all as I have thousands of lines of data and would rather not go through by hand if at all possible.
 
Upvote 0
Hey, it took a while to come up with this code, so I'm glad to see that I'm the first to post a VBA solution. Also, this was in a testing phase so it works on the condition that it is just checking for the optimal schedule in the second row of the matrix that you posted.

If this is what you were looking for, then I can tailor it to do what you need. I used the same date as my test row, and for the date of 1/10/2007, I got an answer of 47.97 as the optimal schedule total selecting columns S:V.

Here's the code

Code: 
Function aFact(x As Single)
Dim Total As Long
    Do Until x = 0
        Total = Total + x
        x = x - 1
    Loop
aFact = Total
End Function

Sub MXL()
Dim r As Range, c1 As Range, c2 As Range, c0 As Range
Dim iRow As Single, Tot As Single, Ctr1 As Single, Ctr2 As Single, i As Single, y As Single, z As Single, temp As Single
Dim iMax As Single
Dim arr() As Single
Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0
iRow = 2
Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
    Do Until c1 >= 0
        Set c1 = c1.Offset(0, 1)
    Loop
    Set c0 = Cells(iRow, c1.Column())
    Do Until c2 >= 0
        Set c2 = c2.Offset(0, -1)
    Loop
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, c0.Column())
        Set r = r.Resize(1, Tot)
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
            End If
                Do Until i = 0
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
iMax = WorksheetFunction.Max(arr())

Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0
iRow = 2
Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
    Do Until c1 >= 0
        Set c1 = c1.Offset(0, 1)
    Loop
    Set c0 = Cells(iRow, c1.Column())
    Do Until c2 >= 0
        Set c2 = c2.Offset(0, -1)
    Loop
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, c0.Column())
        Set r = r.Resize(1, Tot)
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
            End If
                Do Until i = 0
                    If WorksheetFunction.Sum(r) = iMax Then Exit Sub
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop

End Sub

By the way, I'm sure this code could be cleaned up a bit, so if anyone has some tips on that, it would be welcome.
 
Upvote 0
Thank you very much for the help!

I ran the code, it didn't run into any errors, but I don't see any output. Where should I be looking?
 
Upvote 0
A couple of things.

1. I have been testing to get it to work with all rows and it still needs work, especially for rows that have all negatives.

2. I changed the code to highlight the optimal range in yellow.

You can run the code on the original matrix that you posted and it should highlight the optimal range on the selected date.

Here it is.

Code: 
Function aFact(x As Single)
Dim Total As Long
    Do Until x = 0
        Total = Total + x
        x = x - 1
    Loop
aFact = Total
End Function

Sub MXL()
Application.ScreenUpdating = False
Dim r As Range, c1 As Range, c2 As Range, c0 As Range
Dim iRow As Single, Tot As Single, Ctr1 As Single, Ctr2 As Single, i As Single, y As Single, z As Single, temp As Single
Dim iMax As Single
Dim arr() As Single
Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0
iRow = 11

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
    Do Until c1 >= 0
        Set c1 = c1.Offset(0, 1)
    Loop
    Set c0 = Cells(iRow, c1.Column())
    Do Until c2 >= 0
        Set c2 = c2.Offset(0, -1)
    Loop
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
r.Select
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, c0.Column())
        Set r = r.Resize(1, Tot)
        r.Select
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
iMax = WorksheetFunction.Max(arr())

Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0
iRow = 11

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
    Do Until c1 >= 0
        Set c1 = c1.Offset(0, 1)
    Loop
    Set c0 = Cells(iRow, c1.Column())
    Do Until c2 >= 0
        Set c2 = c2.Offset(0, -1)
    Loop
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, c0.Column())
        Set r = r.Resize(1, Tot)
        r.Select
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    If WorksheetFunction.Sum(r) = iMax Then
                        r.Interior.ColorIndex = 6
                    End If
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Changed it a little bit again and now it is working for the following row as well. Now just to figure out how to loop through each row and deal with all negative values.
 
Upvote 0
OK, I think this will do everything. Tested it out and it seems to work.

Let me know what you think.

Code: 
Function aFact(x As Single)
Dim Total As Long
    Do Until x = 0
        Total = Total + x
        x = x - 1
    Loop
aFact = Total
End Function

Sub MXL()
Application.ScreenUpdating = False
Dim r As Range, c1 As Range, c2 As Range, c0 As Range
Dim iRow As Single, Tot As Single, Ctr1 As Single, Ctr2 As Single, i As Single, y As Single, z As Single, temp As Single
Dim iMax As Single
Dim arr() As Single
iRow = 3
Do Until iRow = 19
Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
r.Select
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, 2)
        Set r = r.Resize(1, Tot)
        r.Select
            If i = 0 Then
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
iMax = WorksheetFunction.Max(arr())

Ctr1 = 0
Ctr2 = 0
i = 0
y = 1
z = 0

Set c1 = Cells(iRow, 2)
Set c2 = Cells(iRow, 25)
Set r = Range(Cells(iRow, c1.Column()), Cells(iRow, c2.Column()))
Tot = WorksheetFunction.CountA(r)
temp = Tot
Ctr1 = aFact(temp)
ReDim arr(1 To Ctr1)
    Do Until Ctr1 = 0
        Set r = Cells(iRow, 2)
        Set r = r.Resize(1, Tot)
        r.Select
            If i = 0 Then
                If WorksheetFunction.Sum(r) = iMax Then
                        r.Interior.ColorIndex = 6
                        GoTo n
                    End If
                arr(y) = WorksheetFunction.Sum(r)
                z = z + 1
                Ctr1 = Ctr1 - 1
                y = y + 1
            End If
                Do Until i = 0
                    If WorksheetFunction.Sum(r) = iMax Then
                        r.Interior.ColorIndex = 6
                        GoTo n
                    End If
                    arr(y) = WorksheetFunction.Sum(r)
                    Set r = r.Offset(0, 1)
                    r.Select
                    i = i - 1
                    y = y + 1
                    Ctr1 = Ctr1 - 1
                Loop
            z = z + 1
            i = z
            Tot = Tot - 1
    Loop
n:
    iRow = iRow + 1
Loop
Application.ScreenUpdating = True
End Sub
 
Upvote 0
It seems to stop at line 18.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th><th>Y</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">0</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td><td style="font-weight: bold;text-align: center;;">6</td><td style="font-weight: bold;text-align: center;;">7</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">10</td><td style="font-weight: bold;text-align: center;;">11</td><td style="font-weight: bold;text-align: center;;">12</td><td style="font-weight: bold;text-align: center;;">13</td><td style="font-weight: bold;text-align: center;;">14</td><td style="font-weight: bold;text-align: center;;">15</td><td style="font-weight: bold;text-align: center;;">16</td><td style="font-weight: bold;text-align: center;;">17</td><td style="font-weight: bold;text-align: center;;">18</td><td style="font-weight: bold;text-align: center;;">19</td><td style="font-weight: bold;text-align: center;;">20</td><td style="font-weight: bold;text-align: center;;">21</td><td style="font-weight: bold;text-align: center;;">22</td><td style="font-weight: bold;text-align: center;;">23</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2007/1/1</td><td style="text-align: right;;">-$21.99</td><td style="text-align: right;;">-$22.28</td><td style="text-align: right;;">-$24.20</td><td style="text-align: right;;">-$25.20</td><td style="text-align: right;;">-$25.13</td><td style="text-align: right;;">-$24.16</td><td style="text-align: right;;">-$22.51</td><td style="text-align: right;;">-$21.76</td><td style="text-align: right;;">-$21.23</td><td style="text-align: right;;">-$20.42</td><td style="text-align: right;;">-$19.67</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.39</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.75</td><td style="text-align: right;;">-$19.87</td><td style="text-align: right;;">-$19.47</td><td style="text-align: right;;">-$14.58</td><td style="text-align: right;;">-$13.31</td><td style="text-align: right;;">-$14.60</td><td style="text-align: right;;">-$15.81</td><td style="text-align: right;;">-$16.93</td><td style="text-align: right;;">-$18.58</td><td style="text-align: right;;">-$19.82</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2007/1/2</td><td style="text-align: right;;">-$18.66</td><td style="text-align: right;;">-$19.19</td><td style="text-align: right;;">-$19.63</td><td style="text-align: right;;">-$19.73</td><td style="text-align: right;;">-$19.44</td><td style="text-align: right;;">-$17.93</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$9.03</td><td style="text-align: right;;">-$7.68</td><td style="text-align: right;;">-$5.25</td><td style="text-align: right;;">-$3.34</td><td style="text-align: right;;">-$6.83</td><td style="text-align: right;;">-$9.50</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$14.02</td><td style="text-align: right;;">-$14.67</td><td style="text-align: right;;">-$14.30</td><td style="text-align: right;background-color: #FFFF00;;">$9.45</td><td style="text-align: right;background-color: #FFFF00;;">$26.92</td><td style="text-align: right;background-color: #FFFF00;;">$12.56</td><td style="text-align: right;background-color: #FFFF00;;">$7.12</td><td style="text-align: right;;">-$1.56</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$14.02</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">2007/1/3</td><td style="text-align: right;;">-$15.79</td><td style="text-align: right;;">-$16.54</td><td style="text-align: right;;">-$16.91</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$16.84</td><td style="text-align: right;;">-$14.69</td><td style="text-align: right;;">-$11.63</td><td style="text-align: right;;">-$2.76</td><td style="text-align: right;;">-$2.10</td><td style="text-align: right;;">-$5.39</td><td style="text-align: right;;">-$5.19</td><td style="text-align: right;;">-$9.40</td><td style="text-align: right;;">-$11.47</td><td style="text-align: right;;">-$13.54</td><td style="text-align: right;;">-$14.53</td><td style="text-align: right;;">-$14.96</td><td style="text-align: right;;">-$14.72</td><td style="text-align: right;background-color: #FFFF00;;">$2.16</td><td style="text-align: right;background-color: #FFFF00;;">$12.48</td><td style="text-align: right;background-color: #FFFF00;;">$1.56</td><td style="text-align: right;;">-$5.17</td><td style="text-align: right;;">-$7.09</td><td style="text-align: right;;">-$14.55</td><td style="text-align: right;;">-$16.03</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">2007/1/4</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$18.43</td><td style="text-align: right;;">-$18.91</td><td style="text-align: right;;">-$19.07</td><td style="text-align: right;;">-$18.61</td><td style="text-align: right;;">-$16.96</td><td style="text-align: right;;">-$13.01</td><td style="text-align: right;;">-$4.21</td><td style="text-align: right;;">-$4.59</td><td style="text-align: right;;">-$8.14</td><td style="text-align: right;;">-$8.56</td><td style="text-align: right;;">-$10.71</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$14.14</td><td style="text-align: right;;">-$14.91</td><td style="text-align: right;;">-$15.39</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$1.65</td><td style="text-align: right;background-color: #FFFF00;;">$10.45</td><td style="text-align: right;;">-$0.57</td><td style="text-align: right;;">-$6.95</td><td style="text-align: right;;">-$10.58</td><td style="text-align: right;;">-$16.11</td><td style="text-align: right;;">-$16.74</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">2007/1/5</td><td style="text-align: right;;">-$18.15</td><td style="text-align: right;;">-$18.92</td><td style="text-align: right;;">-$19.28</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.42</td><td style="text-align: right;;">-$18.27</td><td style="text-align: right;;">-$14.38</td><td style="text-align: right;;">-$7.80</td><td style="text-align: right;;">-$8.06</td><td style="text-align: right;;">-$10.27</td><td style="text-align: right;;">-$10.33</td><td style="text-align: right;;">-$10.97</td><td style="text-align: right;;">-$12.89</td><td style="text-align: right;;">-$13.48</td><td style="text-align: right;;">-$14.19</td><td style="text-align: right;;">-$14.63</td><td style="text-align: right;;">-$14.43</td><td style="text-align: right;;">-$7.40</td><td style="text-align: right;background-color: #FFFF00;;">-$1.32</td><td style="text-align: right;;">-$6.14</td><td style="text-align: right;;">-$10.10</td><td style="text-align: right;;">-$13.70</td><td style="text-align: right;;">-$16.17</td><td style="text-align: right;;">-$17.03</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">2007/1/6</td><td style="text-align: right;;">-$18.85</td><td style="text-align: right;;">-$19.16</td><td style="text-align: right;;">-$19.71</td><td style="text-align: right;;">-$20.41</td><td style="text-align: right;;">-$20.59</td><td style="text-align: right;;">-$20.11</td><td style="text-align: right;;">-$19.03</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$13.64</td><td style="text-align: right;;">-$12.69</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$13.56</td><td style="text-align: right;;">-$13.89</td><td style="text-align: right;;">-$14.97</td><td style="text-align: right;;">-$15.78</td><td style="text-align: right;;">-$16.37</td><td style="text-align: right;;">-$15.41</td><td style="text-align: right;;">-$6.45</td><td style="text-align: right;background-color: #FFFF00;;">-$4.93</td><td style="text-align: right;;">-$8.22</td><td style="text-align: right;;">-$12.29</td><td style="text-align: right;;">-$13.40</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$18.10</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">2007/1/7</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$18.53</td><td style="text-align: right;;">-$19.20</td><td style="text-align: right;;">-$19.51</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.23</td><td style="text-align: right;;">-$17.99</td><td style="text-align: right;;">-$16.39</td><td style="text-align: right;;">-$14.54</td><td style="text-align: right;;">-$13.86</td><td style="text-align: right;;">-$13.98</td><td style="text-align: right;;">-$13.95</td><td style="text-align: right;;">-$14.46</td><td style="text-align: right;;">-$14.92</td><td style="text-align: right;;">-$15.36</td><td style="text-align: right;;">-$15.91</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$1.58</td><td style="text-align: right;background-color: #FFFF00;;">$5.88</td><td style="text-align: right;background-color: #FFFF00;;">$4.58</td><td style="text-align: right;;">-$3.12</td><td style="text-align: right;;">-$9.62</td><td style="text-align: right;;">-$13.90</td><td style="text-align: right;;">-$18.85</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">2007/1/8</td><td style="text-align: right;;">-$18.50</td><td style="text-align: right;;">-$19.49</td><td style="text-align: right;;">-$19.94</td><td style="text-align: right;;">-$20.04</td><td style="text-align: right;;">-$19.52</td><td style="text-align: right;;">-$17.01</td><td style="text-align: right;;">-$10.56</td><td style="text-align: right;;">$6.69</td><td style="text-align: right;;">$6.16</td><td style="text-align: right;;">$3.96</td><td style="text-align: right;;">$2.96</td><td style="text-align: right;;">$2.53</td><td style="text-align: right;;">$0.67</td><td style="text-align: right;;">-$5.20</td><td style="text-align: right;;">-$7.79</td><td style="text-align: right;;">-$9.83</td><td style="text-align: right;;">-$8.74</td><td style="text-align: right;background-color: #FFFF00;;">$14.30</td><td style="text-align: right;background-color: #FFFF00;;">$33.61</td><td style="text-align: right;background-color: #FFFF00;;">$22.69</td><td style="text-align: right;background-color: #FFFF00;;">$10.34</td><td style="text-align: right;background-color: #FFFF00;;">$0.77</td><td style="text-align: right;;">-$11.88</td><td style="text-align: right;;">-$16.30</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">2007/1/10</td><td style="text-align: right;;">-$15.54</td><td style="text-align: right;;">-$16.30</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$16.28</td><td style="text-align: right;;">-$16.09</td><td style="text-align: right;;">-$13.42</td><td style="text-align: right;;">-$0.24</td><td style="text-align: right;;">$15.66</td><td style="text-align: right;;">$9.45</td><td style="text-align: right;;">$5.82</td><td style="text-align: right;;">$1.15</td><td style="text-align: right;;">-$2.61</td><td style="text-align: right;;">-$6.62</td><td style="text-align: right;;">-$11.36</td><td style="text-align: right;;">-$13.87</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$14.77</td><td style="text-align: right;background-color: #FFFF00;;">$3.47</td><td style="text-align: right;background-color: #FFFF00;;">$27.72</td><td style="text-align: right;background-color: #FFFF00;;">$13.26</td><td style="text-align: right;background-color: #FFFF00;;">$3.52</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;;">-$13.57</td><td style="text-align: right;;">-$12.00</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">2007/1/11</td><td style="text-align: right;;">-$12.72</td><td style="text-align: right;;">-$12.70</td><td style="text-align: right;;">-$13.10</td><td style="text-align: right;;">-$13.18</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$10.83</td><td style="text-align: right;background-color: #FFFF00;;">$8.47</td><td style="text-align: right;background-color: #FFFF00;;">$27.11</td><td style="text-align: right;background-color: #FFFF00;;">$20.18</td><td style="text-align: right;background-color: #FFFF00;;">$13.98</td><td style="text-align: right;background-color: #FFFF00;;">$9.22</td><td style="text-align: right;background-color: #FFFF00;;">$5.00</td><td style="text-align: right;background-color: #FFFF00;;">$0.15</td><td style="text-align: right;background-color: #FFFF00;;">-$3.11</td><td style="text-align: right;background-color: #FFFF00;;">-$8.60</td><td style="text-align: right;background-color: #FFFF00;;">-$10.75</td><td style="text-align: right;background-color: #FFFF00;;">-$11.04</td><td style="text-align: right;background-color: #FFFF00;;">$11.87</td><td style="text-align: right;background-color: #FFFF00;;">$34.52</td><td style="text-align: right;background-color: #FFFF00;;">$19.74</td><td style="text-align: right;background-color: #FFFF00;;">$13.07</td><td style="text-align: right;background-color: #FFFF00;;">$3.38</td><td style="text-align: right;;">-$9.55</td><td style="text-align: right;;">-$14.88</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">2007/1/12</td><td style="text-align: right;;">-$14.42</td><td style="text-align: right;;">-$15.60</td><td style="text-align: right;;">-$15.21</td><td style="text-align: right;;">-$15.32</td><td style="text-align: right;;">-$15.30</td><td style="text-align: right;;">-$13.30</td><td style="text-align: right;background-color: #FFFF00;;">$3.88</td><td style="text-align: right;background-color: #FFFF00;;">$18.76</td><td style="text-align: right;background-color: #FFFF00;;">$12.43</td><td style="text-align: right;background-color: #FFFF00;;">$7.30</td><td style="text-align: right;background-color: #FFFF00;;">$2.85</td><td style="text-align: right;;">-$1.37</td><td style="text-align: right;;">-$5.12</td><td style="text-align: right;;">-$8.15</td><td style="text-align: right;;">-$11.07</td><td style="text-align: right;;">-$12.53</td><td style="text-align: right;;">-$11.73</td><td style="text-align: right;;">$4.21</td><td style="text-align: right;;">$22.51</td><td style="text-align: right;;">$12.80</td><td style="text-align: right;;">$2.51</td><td style="text-align: right;;">-$2.92</td><td style="text-align: right;;">-$13.05</td><td style="text-align: right;;">-$14.36</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">2007/1/13</td><td style="text-align: right;;">-$16.78</td><td style="text-align: right;;">-$17.02</td><td style="text-align: right;;">-$18.30</td><td style="text-align: right;;">-$18.99</td><td style="text-align: right;;">-$18.96</td><td style="text-align: right;;">-$18.31</td><td style="text-align: right;;">-$16.45</td><td style="text-align: right;;">-$14.73</td><td style="text-align: right;;">-$10.54</td><td style="text-align: right;;">-$8.03</td><td style="text-align: right;;">-$7.87</td><td style="text-align: right;;">-$9.58</td><td style="text-align: right;;">-$11.28</td><td style="text-align: right;;">-$12.30</td><td style="text-align: right;;">-$13.92</td><td style="text-align: right;;">-$14.88</td><td style="text-align: right;;">-$13.85</td><td style="text-align: right;;">-$3.02</td><td style="text-align: right;background-color: #FFFF00;;">$7.88</td><td style="text-align: right;;">-$3.06</td><td style="text-align: right;;">-$8.26</td><td style="text-align: right;;">-$10.53</td><td style="text-align: right;;">-$13.65</td><td style="text-align: right;;">-$15.98</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">2007/1/14</td><td style="text-align: right;;">-$16.94</td><td style="text-align: right;;">-$17.49</td><td style="text-align: right;;">-$18.60</td><td style="text-align: right;;">-$19.10</td><td style="text-align: right;;">-$19.62</td><td style="text-align: right;;">-$19.22</td><td style="text-align: right;;">-$18.65</td><td style="text-align: right;;">-$17.80</td><td style="text-align: right;;">-$16.21</td><td style="text-align: right;;">-$14.52</td><td style="text-align: right;;">-$14.08</td><td style="text-align: right;;">-$14.07</td><td style="text-align: right;;">-$14.93</td><td style="text-align: right;;">-$15.02</td><td style="text-align: right;;">-$15.51</td><td style="text-align: right;;">-$15.76</td><td style="text-align: right;;">-$14.00</td><td style="text-align: right;;">-$3.55</td><td style="text-align: right;background-color: #FFFF00;;">$9.89</td><td style="text-align: right;background-color: #FFFF00;;">$8.29</td><td style="text-align: right;background-color: #FFFF00;;">$1.97</td><td style="text-align: right;;">-$6.55</td><td style="text-align: right;;">-$11.60</td><td style="text-align: right;;">-$15.96</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">2007/1/15</td><td style="text-align: right;;">-$15.49</td><td style="text-align: right;;">-$17.00</td><td style="text-align: right;;">-$17.72</td><td style="text-align: right;;">-$18.02</td><td style="text-align: right;;">-$17.48</td><td style="text-align: right;;">-$16.31</td><td style="text-align: right;;">-$8.35</td><td style="text-align: right;;">-$1.10</td><td style="text-align: right;;">$1.98</td><td style="text-align: right;;">$6.29</td><td style="text-align: right;;">$8.09</td><td style="text-align: right;;">$6.47</td><td style="text-align: right;;">$1.39</td><td style="text-align: right;;">-$3.17</td><td style="text-align: right;;">-$7.37</td><td style="text-align: right;;">-$8.52</td><td style="text-align: right;;">-$7.04</td><td style="text-align: right;background-color: #FFFF00;;">$12.81</td><td style="text-align: right;background-color: #FFFF00;;">$39.27</td><td style="text-align: right;background-color: #FFFF00;;">$26.56</td><td style="text-align: right;background-color: #FFFF00;;">$13.34</td><td style="text-align: right;background-color: #FFFF00;;">$5.88</td><td style="text-align: right;;">-$8.94</td><td style="text-align: right;;">-$10.94</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">2007/1/16</td><td style="text-align: right;;">-$12.73</td><td style="text-align: right;;">-$14.40</td><td style="text-align: right;;">-$15.15</td><td style="text-align: right;;">-$15.24</td><td style="text-align: right;;">-$15.19</td><td style="text-align: right;;">-$11.16</td><td style="text-align: right;background-color: #FFFF00;;">$5.19</td><td style="text-align: right;background-color: #FFFF00;;">$22.62</td><td style="text-align: right;background-color: #FFFF00;;">$21.09</td><td style="text-align: right;background-color: #FFFF00;;">$17.01</td><td style="text-align: right;background-color: #FFFF00;;">$18.36</td><td style="text-align: right;background-color: #FFFF00;;">$16.65</td><td style="text-align: right;background-color: #FFFF00;;">$11.58</td><td style="text-align: right;background-color: #FFFF00;;">$5.02</td><td style="text-align: right;background-color: #FFFF00;;">-$1.31</td><td style="text-align: right;background-color: #FFFF00;;">-$3.89</td><td style="text-align: right;background-color: #FFFF00;;">-$1.01</td><td style="text-align: right;background-color: #FFFF00;;">$20.76</td><td style="text-align: right;background-color: #FFFF00;;">$51.17</td><td style="text-align: right;background-color: #FFFF00;;">$41.38</td><td style="text-align: right;background-color: #FFFF00;;">$30.23</td><td style="text-align: right;background-color: #FFFF00;;">$17.81</td><td style="text-align: right;;">-$1.28</td><td style="text-align: right;;">-$3.87</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">2007/1/17</td><td style="text-align: right;;">$10.65</td><td style="text-align: right;;">$0.30</td><td style="text-align: right;;">-$3.75</td><td style="text-align: right;;">-$3.76</td><td style="text-align: right;;">-$3.68</td><td style="text-align: right;background-color: #FFFF00;;">$14.52</td><td style="text-align: right;background-color: #FFFF00;;">$43.79</td><td style="text-align: right;background-color: #FFFF00;;">$51.48</td><td style="text-align: right;background-color: #FFFF00;;">$36.40</td><td style="text-align: right;background-color: #FFFF00;;">$30.79</td><td style="text-align: right;background-color: #FFFF00;;">$32.70</td><td style="text-align: right;background-color: #FFFF00;;">$27.73</td><td style="text-align: right;background-color: #FFFF00;;">$17.40</td><td style="text-align: right;background-color: #FFFF00;;">$9.27</td><td style="text-align: right;background-color: #FFFF00;;">$2.80</td><td style="text-align: right;background-color: #FFFF00;;">$0.11</td><td style="text-align: right;background-color: #FFFF00;;">$1.90</td><td style="text-align: right;background-color: #FFFF00;;">$27.69</td><td style="text-align: right;background-color: #FFFF00;;">$55.91</td><td style="text-align: right;background-color: #FFFF00;;">$46.38</td><td style="text-align: right;background-color: #FFFF00;;">$33.33</td><td style="text-align: right;background-color: #FFFF00;;">$24.35</td><td style="text-align: right;background-color: #FFFF00;;">$6.11</td><td style="text-align: right;background-color: #FFFF00;;">$2.42</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">2007/1/18</td><td style="text-align: right;;">-$2.73</td><td style="text-align: right;;">-$7.21</td><td style="text-align: right;;">-$7.82</td><td style="text-align: right;;">-$7.88</td><td style="text-align: right;;">-$6.38</td><td style="text-align: right;background-color: #FFFF00;;">$0.48</td><td style="text-align: right;background-color: #FFFF00;;">$12.33</td><td style="text-align: right;background-color: #FFFF00;;">$34.02</td><td style="text-align: right;background-color: #FFFF00;;">$25.70</td><td style="text-align: right;background-color: #FFFF00;;">$16.88</td><td style="text-align: right;background-color: #FFFF00;;">$16.99</td><td style="text-align: right;background-color: #FFFF00;;">$15.05</td><td style="text-align: right;background-color: #FFFF00;;">$5.58</td><td style="text-align: right;background-color: #FFFF00;;">$1.65</td><td style="text-align: right;background-color: #FFFF00;;">-$6.14</td><td style="text-align: right;background-color: #FFFF00;;">-$8.64</td><td style="text-align: right;background-color: #FFFF00;;">-$6.16</td><td style="text-align: right;background-color: #FFFF00;;">$8.57</td><td style="text-align: right;background-color: #FFFF00;;">$35.00</td><td style="text-align: right;background-color: #FFFF00;;">$26.56</td><td style="text-align: right;background-color: #FFFF00;;">$18.33</td><td style="text-align: right;background-color: #FFFF00;;">$10.62</td><td style="text-align: right;;">-$2.68</td><td style="text-align: right;;">-$7.32</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2007/1/19</td><td style="text-align: right;;">-$14.09</td><td style="text-align: right;;">-$16.69</td><td style="text-align: right;;">-$17.27</td><td style="text-align: right;;">-$17.35</td><td style="text-align: right;;">-$17.33</td><td style="text-align: right;;">-$11.51</td><td style="text-align: right;;">$3.05</td><td style="text-align: right;;">$22.12</td><td style="text-align: right;;">$16.84</td><td style="text-align: right;;">$12.06</td><td style="text-align: right;;">$15.76</td><td style="text-align: right;;">$12.89</td><td style="text-align: right;;">$5.11</td><td style="text-align: right;;">$0.12</td><td style="text-align: right;;">-$8.02</td><td style="text-align: right;;">-$10.65</td><td style="text-align: right;;">-$9.26</td><td style="text-align: right;;">$7.18</td><td style="text-align: right;;">$29.37</td><td style="text-align: right;;">$15.30</td><td style="text-align: right;;">$9.64</td><td style="text-align: right;;">$3.58</td><td style="text-align: right;;">-$7.81</td><td style="text-align: right;;">-$10.11</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">2007/1/20</td><td style="text-align: right;;">-$12.98</td><td style="text-align: right;;">-$13.52</td><td style="text-align: right;;">-$15.82</td><td style="text-align: right;;">-$16.72</td><td style="text-align: right;;">-$17.14</td><td style="text-align: right;;">-$14.63</td><td style="text-align: right;;">-$12.10</td><td style="text-align: right;;">-$11.65</td><td style="text-align: right;;">-$6.75</td><td style="text-align: right;;">-$1.09</td><td style="text-align: right;;">$2.63</td><td style="text-align: right;;">-$3.23</td><td style="text-align: right;;">-$9.51</td><td style="text-align: right;;">-$14.42</td><td style="text-align: right;;">-$16.98</td><td style="text-align: right;;">-$17.65</td><td style="text-align: right;;">-$14.91</td><td style="text-align: right;;">-$3.63</td><td style="text-align: right;;">$6.46</td><td style="text-align: right;;">-$1.93</td><td style="text-align: right;;">-$9.18</td><td style="text-align: right;;">-$10.18</td><td style="text-align: right;;">-$12.11</td><td style="text-align: right;;">-$14.42</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">2007/1/21</td><td style="text-align: right;;">-$17.04</td><td style="text-align: right;;">-$17.32</td><td style="text-align: right;;">-$18.14</td><td style="text-align: right;;">-$18.89</td><td style="text-align: right;;">-$19.36</td><td style="text-align: right;;">-$18.86</td><td style="text-align: right;;">-$18.42</td><td style="text-align: right;;">-$17.91</td><td style="text-align: right;;">-$15.66</td><td style="text-align: right;;">-$14.96</td><td style="text-align: right;;">-$13.22</td><td style="text-align: right;;">-$13.42</td><td style="text-align: right;;">-$14.20</td><td style="text-align: right;;">-$16.13</td><td style="text-align: right;;">-$16.60</td><td style="text-align: right;;">-$16.86</td><td style="text-align: right;;">-$13.91</td><td style="text-align: right;;">-$5.47</td><td style="text-align: right;;">$4.79</td><td style="text-align: right;;">$0.73</td><td style="text-align: right;;">-$5.45</td><td style="text-align: right;;">-$9.85</td><td style="text-align: right;;">-$13.92</td><td style="text-align: right;;">-$19.25</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">2007/1/22</td><td style="text-align: right;;">-$19.89</td><td style="text-align: right;;">-$21.04</td><td style="text-align: right;;">-$21.88</td><td style="text-align: right;;">-$22.07</td><td style="text-align: right;;">-$21.88</td><td style="text-align: right;;">-$17.06</td><td style="text-align: right;;">-$8.01</td><td style="text-align: right;;">$12.01</td><td style="text-align: right;;">$9.21</td><td style="text-align: right;;">$7.07</td><td style="text-align: right;;">$7.41</td><td style="text-align: right;;">$7.40</td><td style="text-align: right;;">$0.32</td><td style="text-align: right;;">-$5.38</td><td style="text-align: right;;">-$11.79</td><td style="text-align: right;;">-$14.64</td><td style="text-align: right;;">-$10.23</td><td style="text-align: right;;">$3.80</td><td style="text-align: right;;">$33.59</td><td style="text-align: right;;">$21.04</td><td style="text-align: right;;">$8.61</td><td style="text-align: right;;">$7.43</td><td style="text-align: right;;">-$10.24</td><td style="text-align: right;;">-$18.03</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">2007/1/23</td><td style="text-align: right;;">-$19.54</td><td style="text-align: right;;">-$20.94</td><td style="text-align: right;;">-$22.02</td><td style="text-align: right;;">-$22.25</td><td style="text-align: right;;">-$22.01</td><td style="text-align: right;;">-$17.63</td><td style="text-align: right;;">-$5.54</td><td style="text-align: right;;">$12.31</td><td style="text-align: right;;">$12.71</td><td style="text-align: right;;">$8.96</td><td style="text-align: right;;">$8.66</td><td style="text-align: right;;">$5.95</td><td style="text-align: right;;">-$3.23</td><td style="text-align: right;;">-$8.42</td><td style="text-align: right;;">-$14.19</td><td style="text-align: right;;">-$16.22</td><td style="text-align: right;;">-$12.87</td><td style="text-align: right;;">$1.69</td><td style="text-align: right;;">$28.57</td><td style="text-align: right;;">$13.76</td><td style="text-align: right;;">$6.19</td><td style="text-align: right;;">$1.95</td><td style="text-align: right;;">-$11.94</td><td style="text-align: right;;">-$16.83</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">2007/1/24</td><td style="text-align: right;;">-$19.60</td><td style="text-align: right;;">-$21.22</td><td style="text-align: right;;">-$21.49</td><td style="text-align: right;;">-$21.67</td><td style="text-align: right;;">-$20.98</td><td style="text-align: right;;">-$17.05</td><td style="text-align: right;;">-$8.30</td><td style="text-align: right;;">$17.42</td><td style="text-align: right;;">$15.27</td><td style="text-align: right;;">$9.94</td><td style="text-align: right;;">$10.15</td><td style="text-align: right;;">$8.27</td><td style="text-align: right;;">-$1.34</td><td style="text-align: right;;">-$5.77</td><td style="text-align: right;;">-$11.60</td><td style="text-align: right;;">-$13.42</td><td style="text-align: right;;">-$9.60</td><td style="text-align: right;;">$4.62</td><td style="text-align: right;;">$34.69</td><td style="text-align: right;;">$19.37</td><td style="text-align: right;;">$9.05</td><td style="text-align: right;;">$6.84</td><td style="text-align: right;;">-$10.14</td><td style="text-align: right;;">-$12.61</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">2007/1/25</td><td style="text-align: right;;">-$8.41</td><td style="text-align: right;;">-$9.95</td><td style="text-align: right;;">-$10.84</td><td style="text-align: right;;">-$11.10</td><td style="text-align: right;;">-$10.97</td><td style="text-align: right;;">-$5.55</td><td style="text-align: right;;">$10.80</td><td style="text-align: right;;">$46.23</td><td style="text-align: right;;">$33.71</td><td style="text-align: right;;">$31.10</td><td style="text-align: right;;">$30.77</td><td style="text-align: right;;">$28.57</td><td style="text-align: right;;">$18.31</td><td style="text-align: right;;">$14.74</td><td style="text-align: right;;">$7.84</td><td style="text-align: right;;">$4.13</td><td style="text-align: right;;">$8.40</td><td style="text-align: right;;">$28.60</td><td style="text-align: right;;">$67.47</td><td style="text-align: right;;">$51.00</td><td style="text-align: right;;">$36.24</td><td style="text-align: right;;">$29.91</td><td style="text-align: right;;">$5.47</td><td style="text-align: right;;">-$5.04</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">2007/1/26</td><td style="text-align: right;;">-$6.71</td><td style="text-align: right;;">-$10.56</td><td style="text-align: right;;">-$13.03</td><td style="text-align: right;;">-$13.28</td><td style="text-align: right;;">-$13.04</td><td style="text-align: right;;">-$5.88</td><td style="text-align: right;;">$11.47</td><td style="text-align: right;;">$45.80</td><td style="text-align: right;;">$33.97</td><td style="text-align: right;;">$28.03</td><td style="text-align: right;;">$28.09</td><td style="text-align: right;;">$21.96</td><td style="text-align: right;;">$11.73</td><td style="text-align: right;;">$4.90</td><td style="text-align: right;;">-$6.00</td><td style="text-align: right;;">-$10.10</td><td style="text-align: right;;">-$8.50</td><td style="text-align: right;;">$5.53</td><td style="text-align: right;;">$45.14</td><td style="text-align: right;;">$27.85</td><td style="text-align: right;;">$16.84</td><td style="text-align: right;;">$10.18</td><td style="text-align: right;;">-$7.27</td><td style="text-align: right;;">-$11.56</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">2007/1/27</td><td style="text-align: right;;">-$13.45</td><td style="text-align: right;;">-$13.85</td><td style="text-align: right;;">-$17.59</td><td style="text-align: right;;">-$19.03</td><td style="text-align: right;;">-$19.95</td><td style="text-align: right;;">-$18.24</td><td style="text-align: right;;">-$17.22</td><td style="text-align: right;;">-$12.57</td><td style="text-align: right;;">-$7.97</td><td style="text-align: right;;">-$1.57</td><td style="text-align: right;;">$0.99</td><td style="text-align: right;;">-$3.16</td><td style="text-align: right;;">-$9.86</td><td style="text-align: right;;">-$14.22</td><td style="text-align: right;;">-$16.73</td><td style="text-align: right;;">-$17.66</td><td style="text-align: right;;">-$14.78</td><td style="text-align: right;;">-$5.70</td><td style="text-align: right;;">$14.08</td><td style="text-align: right;;">$5.83</td><td style="text-align: right;;">-$1.39</td><td style="text-align: right;;">-$8.04</td><td style="text-align: right;;">-$12.39</td><td style="text-align: right;;">-$14.87</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">2007/1/28</td><td style="text-align: right;;">-$15.62</td><td style="text-align: right;;">-$16.23</td><td style="text-align: right;;">-$17.56</td><td style="text-align: right;;">-$19.29</td><td style="text-align: right;;">-$20.10</td><td style="text-align: right;;">-$19.77</td><td style="text-align: right;;">-$20.42</td><td style="text-align: right;;">-$19.17</td><td style="text-align: right;;">-$15.62</td><td style="text-align: right;;">-$12.03</td><td style="text-align: right;;">-$11.58</td><td style="text-align: right;;">-$12.47</td><td style="text-align: right;;">-$14.08</td><td style="text-align: right;;">-$15.63</td><td style="text-align: right;;">-$17.48</td><td style="text-align: right;;">-$17.94</td><td style="text-align: right;;">-$15.24</td><td style="text-align: right;;">-$1.45</td><td style="text-align: right;;">$19.65</td><td style="text-align: right;;">$14.47</td><td style="text-align: right;;">$6.01</td><td style="text-align: right;;">$0.90</td><td style="text-align: right;;">-$7.68</td><td style="text-align: right;;">-$11.04</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">2007/1/29</td><td style="text-align: right;;">-$7.23</td><td style="text-align: right;;">-$8.91</td><td style="text-align: right;;">-$10.23</td><td style="text-align: right;;">-$10.49</td><td style="text-align: right;;">-$9.60</td><td style="text-align: right;;">-$3.36</td><td style="text-align: right;;">$13.62</td><td style="text-align: right;;">$43.29</td><td style="text-align: right;;">$31.88</td><td style="text-align: right;;">$28.22</td><td style="text-align: right;;">$28.45</td><td style="text-align: right;;">$24.84</td><td style="text-align: right;;">$13.58</td><td style="text-align: right;;">$8.17</td><td style="text-align: right;;">$1.65</td><td style="text-align: right;;">-$5.47</td><td style="text-align: right;;">-$2.02</td><td style="text-align: right;;">$16.15</td><td style="text-align: right;;">$59.93</td><td style="text-align: right;;">$52.76</td><td style="text-align: right;;">$38.14</td><td style="text-align: right;;">$28.22</td><td style="text-align: right;;">$3.53</td><td style="text-align: right;;">-$7.43</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">2007/1/30</td><td style="text-align: right;;">-$5.23</td><td style="text-align: right;;">-$9.33</td><td style="text-align: right;;">-$11.89</td><td style="text-align: right;;">-$12.06</td><td style="text-align: right;;">-$10.74</td><td style="text-align: right;;">-$3.51</td><td style="text-align: right;;">$19.55</td><td style="text-align: right;;">$44.62</td><td style="text-align: right;;">$31.74</td><td style="text-align: right;;">$31.45</td><td style="text-align: right;;">$30.00</td><td style="text-align: right;;">$24.13</td><td style="text-align: right;;">$10.76</td><td style="text-align: right;;">$5.16</td><td style="text-align: right;;">-$0.30</td><td style="text-align: right;;">-$6.13</td><td style="text-align: right;;">-$0.56</td><td style="text-align: right;;">$16.50</td><td style="text-align: right;;">$58.35</td><td style="text-align: right;;">$51.55</td><td style="text-align: right;;">$39.13</td><td style="text-align: right;;">$27.40</td><td style="text-align: right;;">$5.80</td><td style="text-align: right;;">$0.86</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">2007/1/31</td><td style="text-align: right;;">$0.39</td><td style="text-align: right;;">-$2.01</td><td style="text-align: right;;">-$2.24</td><td style="text-align: right;;">-$2.94</td><td style="text-align: right;;">-$2.43</td><td style="text-align: right;;">$3.34</td><td style="text-align: right;;">$26.48</td><td style="text-align: right;;">$52.27</td><td style="text-align: right;;">$41.28</td><td style="text-align: right;;">$35.11</td><td style="text-align: right;;">$32.89</td><td style="text-align: right;;">$25.29</td><td style="text-align: right;;">$11.68</td><td style="text-align: right;;">$5.13</td><td style="text-align: right;;">-$0.82</td><td style="text-align: right;;">-$5.39</td><td style="text-align: right;;">-$2.65</td><td style="text-align: right;;">$15.62</td><td style="text-align: right;;">$51.88</td><td style="text-align: right;;">$48.46</td><td style="text-align: right;;">$35.11</td><td style="text-align: right;;">$26.54</td><td style="text-align: right;;">$5.63</td><td style="text-align: right;;">-$1.85</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;">2007/2/1</td><td style="text-align: right;;">$5.32</td><td style="text-align: right;;">$1.54</td><td style="text-align: right;;">-$1.03</td><td style="text-align: right;;">-$0.75</td><td style="text-align: right;;">-$1.58</td><td style="text-align: right;;">$6.14</td><td style="text-align: right;;">$27.77</td><td style="text-align: right;;">$43.84</td><td style="text-align: right;;">$33.46</td><td style="text-align: right;;">$30.08</td><td style="text-align: right;;">$28.51</td><td style="text-align: right;;">$22.18</td><td style="text-align: right;;">$11.17</td><td style="text-align: right;;">$5.75</td><td style="text-align: right;;">-$2.37</td><td style="text-align: right;;">-$6.01</td><td style="text-align: right;;">-$3.35</td><td style="text-align: right;;">$13.11</td><td style="text-align: right;;">$51.10</td><td style="text-align: right;;">$47.20</td><td style="text-align: right;;">$31.34</td><td style="text-align: right;;">$22.73</td><td style="text-align: right;;">$3.97</td><td style="text-align: right;;">$2.65</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;">2007/2/2</td><td style="text-align: right;;">-$1.97</td><td style="text-align: right;;">-$3.42</td><td style="text-align: right;;">-$7.06</td><td style="text-align: right;;">-$8.29</td><td style="text-align: right;;">-$10.00</td><td style="text-align: right;;">-$1.99</td><td style="text-align: right;;">$20.25</td><td style="text-align: right;;">$39.98</td><td style="text-align: right;;">$37.34</td><td style="text-align: right;;">$32.19</td><td style="text-align: right;;">$32.72</td><td style="text-align: right;;">$24.17</td><td style="text-align: right;;">$17.48</td><td style="text-align: right;;">$9.86</td><td style="text-align: right;;">$2.87</td><td style="text-align: right;;">-$1.87</td><td style="text-align: right;;">-$0.26</td><td style="text-align: right;;">$13.57</td><td style="text-align: right;;">$52.72</td><td style="text-align: right;;">$46.41</td><td style="text-align: right;;">$32.60</td><td style="text-align: right;;">$23.91</td><td style="text-align: right;;">$5.36</td><td style="text-align: right;;">$2.21</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: right;;">2007/2/3</td><td style="text-align: right;;">$0.94</td><td style="text-align: right;;">-$1.96</td><td style="text-align: right;;">-$6.66</td><td style="text-align: right;;">-$7.54</td><td style="text-align: right;;">-$7.78</td><td style="text-align: right;;">-$6.63</td><td style="text-align: right;;">$0.83</td><td style="text-align: right;;">$15.66</td><td style="text-align: right;;">$18.78</td><td style="text-align: right;;">$24.00</td><td style="text-align: right;;">$29.63</td><td style="text-align: right;;">$23.35</td><td style="text-align: right;;">$12.55</td><td style="text-align: right;;">$6.53</td><td style="text-align: right;;">-$2.07</td><td style="text-align: right;;">-$5.53</td><td style="text-align: right;;">-$1.38</td><td style="text-align: right;;">$12.80</td><td style="text-align: right;;">$48.50</td><td style="text-align: right;;">$41.53</td><td style="text-align: right;;">$29.89</td><td style="text-align: right;;">$19.52</td><td style="text-align: right;;">$6.07</td><td style="text-align: right;;">-$0.67</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;">2007/2/4</td><td style="text-align: right;;">$7.64</td><td style="text-align: right;;">$2.87</td><td style="text-align: right;;">-$1.15</td><td style="text-align: right;;">-$1.93</td><td style="text-align: right;;">-$3.77</td><td style="text-align: right;;">-$1.97</td><td style="text-align: right;;">$0.78</td><td style="text-align: right;;">$18.39</td><td style="text-align: right;;">$28.14</td><td style="text-align: right;;">$30.99</td><td style="text-align: right;;">$32.46</td><td style="text-align: right;;">$26.41</td><td style="text-align: right;;">$17.00</td><td style="text-align: right;;">$10.01</td><td style="text-align: right;;">$4.83</td><td style="text-align: right;;">$2.87</td><td style="text-align: right;;">$8.95</td><td style="text-align: right;;">$23.40</td><td style="text-align: right;;">$73.09</td><td style="text-align: right;;">$68.33</td><td style="text-align: right;;">$55.81</td><td style="text-align: right;;">$43.28</td><td style="text-align: right;;">$26.86</td><td style="text-align: right;;">$10.09</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: right;;">2007/2/5</td><td style="text-align: right;;">$2.07</td><td style="text-align: right;;">$2.03</td><td style="text-align: right;;">$2.05</td><td style="text-align: right;;">$2.03</td><td style="text-align: right;;">$2.04</td><td style="text-align: right;;">$15.79</td><td style="text-align: right;;">$52.05</td><td style="text-align: right;;">$72.50</td><td style="text-align: right;;">$68.79</td><td style="text-align: right;;">$61.20</td><td style="text-align: right;;">$64.18</td><td style="text-align: right;;">$54.01</td><td style="text-align: right;;">$43.67</td><td style="text-align: right;;">$33.70</td><td style="text-align: right;;">$24.20</td><td style="text-align: right;;">$17.66</td><td style="text-align: right;;">$22.90</td><td style="text-align: right;;">$38.64</td><td style="text-align: right;;">$79.95</td><td style="text-align: right;;">$81.56</td><td style="text-align: right;;">$64.08</td><td style="text-align: right;;">$58.84</td><td style="text-align: right;;">$37.27</td><td style="text-align: right;;">$25.55</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: right;;">2007/2/6</td><td style="text-align: right;;">$12.42</td><td style="text-align: right;;">$6.75</td><td style="text-align: right;;">$6.69</td><td style="text-align: right;;">$6.71</td><td style="text-align: right;;">$6.22</td><td style="text-align: right;;">$20.37</td><td style="text-align: right;;">$57.57</td><td style="text-align: right;;">$88.87</td><td style="text-align: right;;">$80.96</td><td style="text-align: right;;">$75.08</td><td style="text-align: right;;">$74.46</td><td style="text-align: right;;">$64.24</td><td style="text-align: right;;">$53.91</td><td style="text-align: right;;">$43.05</td><td style="text-align: right;;">$29.94</td><td style="text-align: right;;">$21.83</td><td style="text-align: right;;">$26.78</td><td style="text-align: right;;">$38.72</td><td style="text-align: right;;">$93.05</td><td style="text-align: right;;">$91.70</td><td style="text-align: right;;">$74.08</td><td style="text-align: right;;">$68.59</td><td style="text-align: right;;">$37.07</td><td style="text-align: right;;">$23.03</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Nothing else is highlighted (though it should be). Also, is there any way to just give the start/end of the optimal time period? I have many years of data, so going through visually is going to be a burden.
 
Upvote 0
Yeah, I wasn't sure how far down it would have to go.

All you need to do is modify the line of code

Code: 
Do Until iRow = 19

to whatever your last row is +1. So if you have 37 rows like in your last post the code should be

Code: 
Do Until iRow = 38
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Adding a custom message.
Replies
1
Views
444
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
V
  • Question
Issue with scraping with VBA
Replies
1
Views
520
John_w
J
E
  • Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
356
esben
E
E
  • Question
Create mails with table - standard header specific row
Replies
1
Views
959
esben
E

Forum statistics

Threads
1,215,343
Messages
6,124,405
Members
449,157
Latest member
mytux

Share this page

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