Need Adjustments for Macro

rwmill9716

Active Member
Joined
May 20, 2006
Messages
434
This macro reads Cells BY6 and BZ6 which are the code numbers found in Row 4. It then copies those columns into Cols BY and BZ starting at row 8.

My problem is that the data set has empty cells, and the macro is just copying to the first empty cell (the remaining data in Cols BY and BZ are data left from the last run). I need the macro to copy 500 Rows and not determine the range of a particular column by sensing the first empty cell.

Thanks for your help.

Code:
Sub Graph1()
Dim Found As Range

Set Found = Sheets("Graph").Rows(4).Find(what:=Sheets("Graph").Range("bz6").Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub

With Sheets("Graph")
    .Range(Found, Found.End(xlDown)).Copy Destination:=Sheets("Graph").Range("Bz8")
End With

Set Found = Sheets("Graph").Rows(4).Find(what:=Sheets("Graph").Range("bY6").Value, LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then Exit Sub

With Sheets("Graph")
    .Range(Found, Found.End(xlDown)).Copy Destination:=Sheets("Graph").Range("BY8")
End With


End Sub





<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>BJ</th><th>BK</th><th>BL</th><th>BM</th><th>BN</th><th>BO</th><th>BP</th><th>BQ</th><th>BR</th><th>BS</th><th>BT</th><th>BU</th><th>BV</th><th>BW</th><th>BX</th><th>BY</th><th>BZ</th><th>CA</th><th>CB</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Catalyst Conc., %</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ctl g</td><td style="text-align: right;;">0.424881</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">60</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">62</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">63</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">64</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Catalyst Conc., %</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">X</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;">Y</td><td style="text-align: right;border-left: 1px solid black;;">54.74333</td><td style="text-align: right;;">99.63659</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;;">32.75235</td><td style="text-align: right;border-top: 1px solid black;;">0.00652</td><td style="text-align: right;border-top: 1px solid black;;">95.86662</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;">Code</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">61</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">62</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style=";"> </td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">32.14485</td><td style="text-align: right;;">0.006465</td><td style="text-align: right;;">121.0872</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.AMC</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">AMC in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td><td style=";">Correlation</td><td style="text-align: right;;">0.651829</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">31.83844</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.ACN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">ACNin CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">32.10557</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.ADN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">ADN in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td><td style=";">Y</td><td style=";">X</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">32.3727</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.BHMT</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">BHMT in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;border-top: 1px solid black;;">0.00652</td><td style="text-align: right;border-top: 1px solid black;;">95.86662</td><td style="text-align: right;;">39.71785</td><td style="text-align: right;;">14.93756</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">33.11391</td><td style="text-align: right;;">0.007024</td><td style="text-align: right;;">96.28086</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.CAT</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">CAT in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: right;;">0.006465</td><td style="text-align: right;;">121.0872</td><td style="text-align: right;;">40.82789</td><td style="text-align: right;;">10.95147</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">32.63983</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.DAMP</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">DAMP in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">41.80643</td><td style="text-align: right;;">41.80643</td><td style="text-align: right;;">10.24193</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">32.90696</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.DCH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">DCH in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">4</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">42.63768</td><td style="text-align: right;;">42.63768</td><td style="text-align: right;;">10.4913</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">33.17409</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.H2O</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">H2O in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">5</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">43.46892</td><td style="text-align: right;;">43.46892</td><td style="text-align: right;;">10.74068</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">33.44122</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.HMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">HMD in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: right;;">0.007024</td><td style="text-align: right;;">44.16726</td><td style="text-align: right;;">44.16726</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">33.70835</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">142.3218</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.HMI</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">HMI in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">44.30017</td><td style="text-align: right;;">44.30017</td><td style="text-align: right;;">10.99005</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">33.97548</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">106.3712</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.PACN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">PACN in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">8</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">45.13142</td><td style="text-align: right;;">45.13142</td><td style="text-align: right;;">11.23943</td></tr></tbody></table><p style="width:3em;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">Graph</p><br /><br />
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

rwmill9716

Active Member
Joined
May 20, 2006
Messages
434
Some of the columns were cut off so I repost the data.

<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 /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>BK</th><th>BL</th><th>BM</th><th>BN</th><th>BO</th><th>BP</th><th>BQ</th><th>BR</th><th>BS</th><th>BT</th><th>BU</th><th>BV</th><th>BW</th><th>BX</th><th>BY</th><th>BZ</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">62</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">63</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">64</td><td style="text-align: center;border-top: 1px solid black;background-color: #C0C0C0;;">65</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td><td style="text-align: right;border-bottom: 1px solid black;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;border-bottom: 1px solid black;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">X</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;">Y</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;;">0.00652</td><td style="text-align: right;border-top: 1px solid black;;">95.86662</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;">Code</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">61</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #FFFF00;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">0.006465</td><td style="text-align: right;;">121.0872</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.AMC</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">AMC in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #969696;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.ACN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">ACNin CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">61</td><td style="text-align: center;border-top: 1px solid black;border-bottom: 1px solid black;background-color: #C0C0C0;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.ADN</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">ADN in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Caustic/Cat. Ratio</td><td style="font-weight: bold;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">Cat Conc</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.BHMT</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">BHMT in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: right;border-top: 1px solid black;;">0.00652</td><td style="text-align: right;border-top: 1px solid black;;">95.86662</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">0.007024</td><td style="text-align: right;;">96.28086</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.CAT</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">CAT in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">2</td><td style="text-align: right;;">0.006465</td><td style="text-align: right;;">121.0872</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.DAMP</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">DAMP in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">3</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">41.80643</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.DCH</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">DCH in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">7</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">4</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">42.63768</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.H2O</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">H2O in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">5</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;">43.46892</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">0.007</td><td style="text-align: right;;"></td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;border-right: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">414CHMD.HMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;background-color: #969696;;">HMD in CHMD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">9</td><td style="text-align: center;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: right;;">0.007024</td><td style="text-align: right;;">44.16726</td></tr></tbody></table><p style="width:3em;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">Graph</p><br /><br />
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,349
Office Version
  1. 365
Platform
  1. Windows
Try this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Graph1()<br>  <SPAN style="color:#00007F">Dim</SPAN> Found <SPAN style="color:#00007F">As</SPAN> Range<br>  <br>  <SPAN style="color:#00007F">With</SPAN> Sheets("Graph")<br>    <SPAN style="color:#00007F">Set</SPAN> Found = .Rows(4).Find(what:=.Range("BZ6").Value, LookIn:=xlValues, lookat:=xlWhole)<br>    <SPAN style="color:#00007F">If</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    .Range(Found, .Cells(.Rows.Count, Found.Column).End(xlUp)).Copy Destination:=.Range("BZ8")<br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Found = .Rows(4).Find(what:=.Range("BY6").Value, LookIn:=xlValues, lookat:=xlWhole)<br>    <SPAN style="color:#00007F">If</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <br>    .Range(Found, .Cells(.Rows.Count, Found.Column).End(xlUp)).Copy Destination:=.Range("BY8")<br>  <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


The logic in your code (& mine) is that if BZ6 is not found in row 4, then we don't even bother to look for BY6 in row 4.
Is that what you intended?
 
Last edited:

rwmill9716

Active Member
Joined
May 20, 2006
Messages
434
Thank you so much, Peter_SSs, that is indeed what I intended and this works link a charm. It enables me to quickly graph XY relationships by supplying the code numbers for hundreds of data. I have another macro that develops all their pairwise correlation functions so this lets me visualize those that are related.

ric miller
 

Watch MrExcel Video

Forum statistics

Threads
1,126,939
Messages
5,621,717
Members
415,853
Latest member
Newlife72

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
Top