Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
I am building charts with non-contiguous ranges. I have struggled
through allot with this but am stumped at getting SeriesCollection(#).XValues to work correctly. the different syntax I have tried either gives me an "unable to set the xvalues property of the series class" error or the results in the source data Catagory X axis Labels look like : ={"$H$1","$J$1","$L$1","$N$1","$P$1","$R$1","$T$1" ,"$V$1","$X$1"} with that curved bracket and sometimes extra quotes... As you can see by the number of .SeriesCollection(1).XValues = xValRng variations, I have given up any logical approach and am now easter egging. Any help will be met with eternal gratitude! Sub AddChart() Dim aChart As Chart Dim shtNm As String Dim chtLoc1 As Range Dim srcRng As Range Dim hdrRow As Range Dim numRows As Integer Dim numColumns As Integer Dim dataTyp As String Dim c As Range Dim firstAdd As String Dim xVal As String Dim xValRng As Range dataTyp = "IMP" shtNm = ActiveSheet.Name ActiveSheet.ChartObjects.Delete Set hdrRow = Range(Range("A1"), Selection.End(xlToRight)) '.Select With hdrRow Set c = .find(dataTyp, LookIn:=xlValues) If Not c Is Nothing Then firstAdd = c.Address Do If c.Address = firstAdd Then xVal = shtNm & "!" & c.Address Else xVal = xVal & "," + shtNm & "!" & c.Address End If Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAdd End If End With shtNm = ActiveSheet.Name Set chtLoc1 = Range("dc_res") Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) With aChart .ChartType = xlLineMarkers Set srcRng = Union(Sheets(shtNm).Range("CODE"), Range("IMP_100_Hz"), Range("IMP_200_Hz"), Range("IMP_400_Hz"), _ Range("IMP_1_kHz"), Range("IMP_2_kHz"), Range("IMP_4_kHz"), _ Range("IMP_10_kHz"), Range("IMP_20_kHz"), Range("IMP_40_kHz")) .SetSourceData Source:=srcRng, _ PlotBy:=xlRows Debug.Print xVal Set xValRng = Range(xVal) xValRng.Select '.SeriesCollection(1).XValues = .xValRng.Address '.SeriesCollection(1).XValues = xValRng.Value '.SeriesCollection(1).XValues = xValRng 'unable to set the xvalues property of the series class '.SeriesCollection(1).XValues = xVal '.SeriesCollection(1).XValues = Worksheets(shtNm).xValRng.Address '.SeriesCollection(1).XValues = Worksheets(shtNm).Range(xVal).Address '.SeriesCollection(1).XValues = Worksheets(shtNm).xVal .SeriesCollection(1).XValues = xValRng .HasTitle = True .ChartTitle.Text = "Configuration " & shtNm & " Impedance" With .Parent .Top = chtLoc1.Offset(10, 0).Top .Left = chtLoc1.Left .Height = 252 .Width = 432 .Name = shtNm & "ChartDev" End With End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
Robert,
I've struck this problem before also. I think there is limit of 10 ranges you can nominate, so check that first. Your code ={"$H$1","$J $1","$L$1","$N$1","$P$1","$R$1","$T$1","$V$1","$X$ 1"} only has 9, so it's probably OK. The code that worked for me was: myxvalues = myxvalues & Cells(1, (x * 4)).Value & "," ActiveChart.SeriesCollection(1).XValues = " " & myxvalues & ")" That's right! A LEADING SPACE and a TRAILING ) (Don't ask me why it works though). So try .SeriesCollection(1).XValues = " " & "$H$1","$J$1","$L$1","$N $1","$P$1","$R$1","$T$1","$V$1","$X$1" & ")" Let me know how you go. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
Here's the Full Code.
I Chart every FOURTH column, so that why there's a lot of mutiplying and dividing by 4 in the code. Function ShowMyChart(MyAddress, MyServerName) MaxGraph = Int(Range(MyAddress).Column / 4) MyRow = Range(MyAddress).Row MyColumn = Range(MyAddress).Column MySeriesName = Cells(MyRow, 2) If MaxGraph 10 Then StartData = MaxGraph - 9 Else StartData = 1 For x = StartData To MaxGraph If Cells(MyRow, (x * 4) + 1).Value < "" Then If x < MaxGraph Then MyDataRange = MyDataRange & Cells(MyRow, (x * 4) + 1).Address & "," myxvalues = myxvalues & Cells(1, (x * 4)).Value & "," Else MyDataRange = MyDataRange & Cells(MyRow, (x * 4) + 1).Address myxvalues = myxvalues & Cells(1, (x * 4)).Value End If End If Next x ChartVolume = Cells(MyRow, 2) Range(MyDataRange).Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.ChartType = xlLineMarkers ActiveChart.SeriesCollection(1).XValues = " " & myxvalues & ")" ActiveChart.SeriesCollection(1).Name = MySeriesName With ActiveChart .HasTitle = True .ChartTitle.Characters.Text = MyServerName & vbCrLf & ChartVolume .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "MB" End With With ActiveChart .HasAxis(xlCategory, xlPrimary) = True .HasAxis(xlValue, xlPrimary) = True End With ActiveChart.Axes(xlCategory, xlPrimary).CategoryType = xlCategoryScale With ActiveChart.Axes(xlCategory) .HasMajorGridlines = False .HasMinorGridlines = False End With With ActiveChart.Axes(xlValue) .HasMajorGridlines = False .HasMinorGridlines = False End With ActiveChart.HasLegend = False ActiveChart.HasDataTable = False ActiveChart.PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With Selection.Fill.PresetTextured PresetTextu=msoTextureBlueTissuePaper Selection.Fill.Visible = True ActiveChart.ChartArea.Select ActiveChart.Axes(xlCategory).Select ActiveChart.PlotArea.Select End Function |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
thanks for the reply Greg. It will not surprise me if a leading and
training space will fix the problem, Ive seen some strange fixes... However, it will **** me off hehe. I just went in and recorded a macro of manually entering the Xvalues and cut that into my code to get me by so I can finish the analysis I need to do with this data. (needed to do days ago) I had to make three versions of the code for three different electrical data types but it works and will save me loads of time, not having to break out 20 or more sheets and create several charts on each one! Ill come back when I'm done and work your suggestion into it so Ill have a more dynamic code I want to open it up so that I can have several different data types and not have to write code each time I need to look at a new data type. Ill post the results Thanks for the help Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
={"$H$1","$J$1","$L$1","$N$1","$P$1","$R$1","$T$1" ,"$V$1","$X$1"}
This means you have put the addresses into an array. You need to produce what Excel and VBA recognize as a range. You could do it with a range like this: ActiveSheet.Range("$H$1,$J$1,$L$1,$N$1,$P$1,$R$1,$ T$1,$V$1,$X$1") Notice the quotes around the entire list of ranges, not around each item. Your code to apply this range to a series' X values is (where srs is the object variable denoting the series): srs.XValues = Worksheets(shtNm).Range("$H$1,$J$1,$L$1,$N$1,$P$1, $R$1,$T$1,$V$1,$X$1") or srs.XValues = "=(Sheet1!R1C8,Sheet1!R1C10,Sheet1!R1C12,Sheet1!R1 C14,Sheet1!R1C14,Sheet1!R1C16,Sheet1!R1C18,Sheet1! R1C20,Sheet1!R1C22)" Notice the use of R1C1 notation; VBA doesn't let you use A1 notation in this statement. The Union you use to define srcRng is inconsistent, because only one of the ranges is referenced to the worksheet. Try this instead: With Worksheets(shtNm) Set srcRng = Union(.Range("CODE"), .Range("IMP_100_Hz"), ..Range("IMP_200_Hz"), .Range("IMP_400_Hz"), _ .Range("IMP_1_kHz"), .Range("IMP_2_kHz"), .Range("IMP_4_kHz"), _ .Range("IMP_10_kHz"), .Range("IMP_20_kHz"), .Range("IMP_40_kHz")) End With A more robust solution would be to set up a range in the worksheet which can be contiguously plotted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Robert H" wrote in message oups.com... I am building charts with non-contiguous ranges. I have struggled through allot with this but am stumped at getting SeriesCollection(#).XValues to work correctly. the different syntax I have tried either gives me an "unable to set the xvalues property of the series class" error or the results in the source data Catagory X axis Labels look like : ={"$H$1","$J$1","$L$1","$N$1","$P$1","$R$1","$T$1" ,"$V$1","$X$1"} with that curved bracket and sometimes extra quotes... As you can see by the number of .SeriesCollection(1).XValues = xValRng variations, I have given up any logical approach and am now easter egging. Any help will be met with eternal gratitude! Sub AddChart() Dim aChart As Chart Dim shtNm As String Dim chtLoc1 As Range Dim srcRng As Range Dim hdrRow As Range Dim numRows As Integer Dim numColumns As Integer Dim dataTyp As String Dim c As Range Dim firstAdd As String Dim xVal As String Dim xValRng As Range dataTyp = "IMP" shtNm = ActiveSheet.Name ActiveSheet.ChartObjects.Delete Set hdrRow = Range(Range("A1"), Selection.End(xlToRight)) '.Select With hdrRow Set c = .find(dataTyp, LookIn:=xlValues) If Not c Is Nothing Then firstAdd = c.Address Do If c.Address = firstAdd Then xVal = shtNm & "!" & c.Address Else xVal = xVal & "," + shtNm & "!" & c.Address End If Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAdd End If End With shtNm = ActiveSheet.Name Set chtLoc1 = Range("dc_res") Set aChart = Charts.Add Set aChart = aChart.Location(Whe=xlLocationAsObject, Name:=shtNm) With aChart .ChartType = xlLineMarkers Set srcRng = Union(Sheets(shtNm).Range("CODE"), Range("IMP_100_Hz"), Range("IMP_200_Hz"), Range("IMP_400_Hz"), _ Range("IMP_1_kHz"), Range("IMP_2_kHz"), Range("IMP_4_kHz"), _ Range("IMP_10_kHz"), Range("IMP_20_kHz"), Range("IMP_40_kHz")) .SetSourceData Source:=srcRng, _ PlotBy:=xlRows Debug.Print xVal Set xValRng = Range(xVal) xValRng.Select '.SeriesCollection(1).XValues = .xValRng.Address '.SeriesCollection(1).XValues = xValRng.Value '.SeriesCollection(1).XValues = xValRng 'unable to set the xvalues property of the series class '.SeriesCollection(1).XValues = xVal '.SeriesCollection(1).XValues = Worksheets(shtNm).xValRng.Address '.SeriesCollection(1).XValues = Worksheets(shtNm).Range(xVal).Address '.SeriesCollection(1).XValues = Worksheets(shtNm).xVal .SeriesCollection(1).XValues = xValRng .HasTitle = True .ChartTitle.Text = "Configuration " & shtNm & " Impedance" With .Parent .Top = chtLoc1.Offset(10, 0).Top .Left = chtLoc1.Left .Height = 252 .Width = 432 .Name = shtNm & "ChartDev" End With End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting chart Xvalue errors
The limit is not 10. It depends on the length of the string used to define
the range. His code falls down for other reasons. Your examples put the values of the indicated cells into a string array, without preserving links to the original cells. This is fine if the chart is a one-off and you don't worry about data changing. The only way inserting a space and a close parenthesis into your string would work is if the string were somehow missing these characters. The last line you suggest fails, but this doesn't fail: activechart.SeriesCollection(1).XValues = "$H$1,$J$1,$L$1,$N$1,$P$1,$R$1,$T$1,$V$1,$X$1" It doesn't do as desired, however, as it uses the cell addresses in the string array, not the values in the cells, as the labels for the category axis. It converts the entry in the Category Labels box of the Source Data - Series dialog into exactly what Robert posted. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Greg Glynn" wrote in message oups.com... Robert, I've struck this problem before also. I think there is limit of 10 ranges you can nominate, so check that first. Your code ={"$H$1","$J $1","$L$1","$N$1","$P$1","$R$1","$T$1","$V$1","$X$ 1"} only has 9, so it's probably OK. The code that worked for me was: myxvalues = myxvalues & Cells(1, (x * 4)).Value & "," ActiveChart.SeriesCollection(1).XValues = " " & myxvalues & ")" That's right! A LEADING SPACE and a TRAILING ) (Don't ask me why it works though). So try .SeriesCollection(1).XValues = " " & "$H$1","$J$1","$L$1","$N $1","$P$1","$R$1","$T$1","$V$1","$X$1" & ")" Let me know how you go. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2graph with the same Xvalue and same Size of graph | Charts and Charting in Excel | |||
Charts with Xvalue and YValue? | Excel Programming | |||
How to get corresponding Xvalue for specific Yvalue from XYScatte. | Charts and Charting in Excel | |||
InitializeComponent() errors, Setting values in another form | Excel Programming | |||
vc++ automation: opening chart as chart window and setting scale | Excel Programming |