Need Adjustments for Macro

[rwmill9716]

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.

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

Excel 2003
	BJ	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA	CB
2	Catalyst Conc., %	Caustic/Cat. Ratio	Cat Conc
3																		ctl g	0.424881
4	60	61	62	63	64	65
5	Catalyst Conc., %	Caustic/Cat. Ratio	Cat Conc													X	Y	54.74333	99.63659
6	32.75235	0.00652	95.86662	1							Code					61	62
7	32.14485	0.006465	121.0872	2					414CHMD.AMC	AMC in CHMD	1							Correlation	0.651829
8	31.83844	0.007		3					414CHMD.ACN	ACNin CHMD	2					61	62
9	32.10557	0.007		4					414CHMD.ADN	ADN in CHMD	3					Caustic/Cat. Ratio	Cat Conc	Y	X
10	32.3727	0.007		5					414CHMD.BHMT	BHMT in CHMD	4				1	0.00652	95.86662	39.71785	14.93756
11	33.11391	0.007024	96.28086	6					414CHMD.CAT	CAT in CHMD	5				2	0.006465	121.0872	40.82789	10.95147
12	32.63983	0.007		7					414CHMD.DAMP	DAMP in CHMD	6				3	0.007	41.80643	41.80643	10.24193
13	32.90696	0.007		8					414CHMD.DCH	DCH in CHMD	7				4	0.007	42.63768	42.63768	10.4913
14	33.17409	0.007		9					414CHMD.H2O	H2O in CHMD	8				5	0.007	43.46892	43.46892	10.74068
15	33.44122	0.007		10					414CHMD.HMD	HMD in CHMD	9				6	0.007024	44.16726	44.16726
16	33.70835	0.007	142.3218	11					414CHMD.HMI	HMI in CHMD	10				7	0.007	44.30017	44.30017	10.99005
17	33.97548	0.007	106.3712	12					414CHMD.PACN	PACN in CHMD	11				8	0.007	45.13142	45.13142	11.23943
Graph

 

[rwmill9716]

Some of the columns were cut off so I repost the data.

Excel 2003
	BK	BL	BM	BN	BO	BP	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ
2	Caustic/Cat. Ratio	Cat Conc
3
4	61	62	63	64	65
5	Caustic/Cat. Ratio	Cat Conc													X	Y
6	0.00652	95.86662	1							Code					61	62
7	0.006465	121.0872	2					414CHMD.AMC	AMC in CHMD	1
8	0.007		3					414CHMD.ACN	ACNin CHMD	2					61	62
9	0.007		4					414CHMD.ADN	ADN in CHMD	3					Caustic/Cat. Ratio	Cat Conc
10	0.007		5					414CHMD.BHMT	BHMT in CHMD	4				1	0.00652	95.86662
11	0.007024	96.28086	6					414CHMD.CAT	CAT in CHMD	5				2	0.006465	121.0872
12	0.007		7					414CHMD.DAMP	DAMP in CHMD	6				3	0.007	41.80643
13	0.007		8					414CHMD.DCH	DCH in CHMD	7				4	0.007	42.63768
14	0.007		9					414CHMD.H2O	H2O in CHMD	8				5	0.007	43.46892
15	0.007		10					414CHMD.HMD	HMD in CHMD	9				6	0.007024	44.16726
Graph

 

[Peter_SSs]

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?

 

[rwmill9716]

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