Chart Problemz

[Khailand]

Hi guys,

I've been working on some code that will create a set of charts from data in the first sheet of a workbook and place the charts in new sheets. The data are grouped in columns. The problem with this code is that it creates three empty charts and actually deletes the data that the first chart refers to in the first sheet. Can anyone explain to me why that is? By the way, in case you're wondering, the data is about heterozygous, wild type, or homozygous genetic mutations, not sexuality.

And here's the code:

[vba]
Sub ChartMutations(NumMutations As Integer)

Dim ChartRange As Range
Dim WTRange As Range
Dim HetRange As Range
Dim HomoRange As Range
Dim MeanRange As Range
Dim SDRange As Range
Dim DBegin As Range
Dim EBegin As Range
Dim DEnd As Range
Dim EEnd As Range
Dim findcell As Range
Dim MeanColumn As Range
Dim SDColumn As Range
Dim WTColumn As Range
Dim HetColumn As Range
Dim HomoColumn As Range

Set MeanColumn = Sheets(1).Cells(4, 4)
Set SDColumn = Sheets(1).Cells(4, 5)
Set WTColumn = Sheets(1).Cells(4, 1)
Set HetColumn = Sheets(1).Cells(4, 2)
Set HomoColumn = Sheets(1).Cells(4, 3)
Set DBegin = Sheets(1).Cells(4, 4)
Set EBegin = Sheets(1).Cells(4, 5)

With Worksheets(1).Range(MeanColumn, MeanColumn.End(xlDown))
Set findcell = .Find(0, LookIn:=xlValues)
Set DEnd = findcell
End With
With Worksheets(1).Range(SDColumn, SDColumn.End(xlDown))
Set findcell = .Find(0, LookIn:=xlValues)
Set EEnd = findcell
End With

Set WTRange = Sheets(1).Range(WTColumn, WTColumn.End(xlDown))
Set HetRange = Sheets(1).Range(HetColumn, HetColumn.End(xlDown))
Set HomoRange = Sheets(1).Range(HomoColumn, HomoColumn.End(xlDown))
Set MeanRange = Sheets(1).Range(DBegin, DEnd)
Set SDRange = Sheets(1).Range(EBegin, EEnd)
Set ChartRange = Union(WTRange, HetRange, HomoRange, MeanRange, SDRange)


'generating charts
For Count = 1 To NumMutations
With Worksheets(Count + 1).ChartObjects.Add _
(Left:=100, Width:=575, Top:=75, Height:=425)
.Chart.SetSourceData Source:=ChartRange
.Chart.ChartType = xlXYScatter
.Chart.PlotBy = xlColumns
End With

'Offsetting Plot Range
DBegin = DBegin.Offset(, 6)
EBegin = EBegin.Offset(, 6)
With Worksheets(1).Range(MeanColumn, MeanColumn.End(xlDown))
Set findcell = .Find(0, LookIn:=xlValues)
Set DEnd = findcell
End With
With Worksheets(1).Range(SDColumn, SDColumn.End(xlDown))
Set findcell = .Find(0, LookIn:=xlValues)
Set EEnd = findcell
End With
MeanColumn = MeanColumn.Offset(, 6)
SDColumn = SDColumn.Offset(, 6)
WTColumn = WTColumn.Offset(, 6)
HetColumn = HetColumn.Offset(, 6)
HomoColumn = HomoColumn.Offset(, 6)
WTRange = Sheets(1).Range(WTColumn, WTColumn.End(xlDown))
HetRange = Sheets(1).Range(HetColumn, HetColumn.End(xlDown))
HomoRange = Sheets(1).Range(HomoColumn, HomoColumn.End(xlDown))
MeanRange = Sheets(1).Range(DBegin, DEnd)
SDRange = Sheets(1).Range(EBegin, EEnd)
ChartRange = Union(WTRange, HetRange, HomoRange, MeanRange, SDRange)

Next Count

End Sub
[/vba]
Thanks in advance for any advice!

[lucas]

in case you're wondering, the data is about heterozygous, wild type, or homozygous genetic mutations, not sexuality.

darn

[Andy Pope]

Hi,

The deleting of data is caused by assign ranges rather than setting a reference in the loop.

Try this fix.
[vba] MeanColumn = MeanColumn.Offset(, 6)
SDColumn = SDColumn.Offset(, 6)
WTColumn = WTColumn.Offset(, 6)
HetColumn = HetColumn.Offset(, 6)
HomoColumn = HomoColumn.Offset(, 6)
Set WTRange = Sheets(1).Range(WTColumn, WTColumn.End(xlDown))
Set HetRange = Sheets(1).Range(HetColumn, HetColumn.End(xlDown))
Set HomoRange = Sheets(1).Range(HomoColumn, HomoColumn.End(xlDown))
Set MeanRange = Sheets(1).Range(DBegin, DEnd)
Set SDRange = Sheets(1).Range(EBegin, EEnd)
ChartRange = Union(WTRange, HetRange, HomoRange, MeanRange, SDRange)
[/vba]

As to blank charts I guess that would depend on what the actual ChartRange reference contained. But it's hard to tell without knowing what your input data looks like.