Dealing with Blank Cells

[rwmill9716]

The following macro reads Cells BY6 and BZ6 to identify columns from which data will be entered in rows 8 and greater. The actual reading for these two parent columns was cut and pasted into columns CA and CB so I could follow the macros action.

The X/Y values match for rows 10 to 14, then a blank X cell (CB15) is encountered. From that point on the Y values continue to match, but the correct X values are lost (they hold the values from the prior loading of a different X code.

Is there some change to the macro code that can more effectively deal with these blank cells, i.e., copy the blank cell and continue with correct values for those following?





Sub Graph1()
Dim Found As Range
Sheets("Graph").Range("C4:C497").Copy Destination:=Sheets("Graph").Range("BZ8")

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
	BX	BY	BZ	CA	CB
5		X	Y
6		2	1
7				Correlation	0.415625
8		2	1
9		ACNin CHMD	AMC in CHMD	Y	X
10	1	14.93756	39.71785	39.71785	14.93756
11	2	10.95147	40.82789	40.82789	10.95147
12	3	10.24193	41.80643	41.80643	10.24193
13	4	10.4913	42.63768	42.63768	10.4913
14	5	10.74068	43.46892	43.46892	10.74068
15	6	74.13886	44.16726	44.16726
16	7	82.5106	44.30017	44.30017	10.99005
17	8	82.44144	45.13142	45.13142	11.23943
18	9	82.53922	45.96267	45.96267	11.4888
19	10	82.61246	46.79392	46.79392	11.73818
Graph

Cell Formulas
Range	Formula
CB7	=CORREL(BY10:BY501,BZ10:BZ501)