Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Problem w/Chart Axis labeling with VBA
I have a worksheet with 26 columns and 95 rows. I select and highlight columns
"v" and "z" Column "v" has the label BE and column "z" has the label PP. I have a vba macro shown below that plots the xyscatterchart and labels the x and y axis. the xyscatterchart always automatically chooses the z column (the furthest righthand column) as the y data points and the v column as the x data points. This data point selection happens no matter which column I select first.(I select a column and then hold down the <ctrl and select a 2nd column). However, when I find the axis labels from the rng=selection, the rng(1).value is always the label of the column I select first. If I select column z first and then column v, the x axis label rgn(1).value =PP while the xyscatterchart makes the z column values as the y axis values. Since the selection of the 2 columns for the xyscatterchart is random, and the y axis will always be the furthest righthand column chosen how do I find the correct column labels without making sure I select the left most column first? Also another weird thing is that when I select any 2 columns in the worksheet, lNumRows=65536 and lNumCols=1. lNumRows should be 95 and lNumcol should be 2. Why is this happening? Thanks for any help with this problem. ==============vba code======================== Sub myScatterChart() 'On Error Resume Next Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim curwk As Worksheet Dim SS As String Dim myCell As Range Dim rng As Range Dim myName As String, sColLabel1 As String, sColLabel2 As String Dim ChartName As String Dim ii As Long Dim lNumRows As Long Dim lNumCols As Long ii = 0 myName = "" SS = ActiveSheet.Name Set rng = Selection With rng lNumRows = .Rows.Count lNumCols = .Columns.Count MsgBox "rngrows=" & lNumRows & " rngcol=" & lNumCols & _ " rng(1)=" & rng(1).VALUE End With For Each myCell In rng If Not IsNumeric(myCell.Value) Then ii = ii + 1 myName = myName & myCell.Value 'MsgBox "ii=" & ii & " " & myCell.Value If ii = 1 Then sColLabel1 = myCell.Value If ii = 2 Then sColLabel2 = myCell.Value End If Next myCell Charts.Add ChartName = ActiveChart.Name If myName < "" And Not myNameExists(myName) Then ActiveChart.Name = myName With ActiveChart .ChartType = xlXYScatter .SetSourceData Source:=rng, PlotBy:=xlColumns .Location Whe=xlLocationAsNewSheet .Move After:=Sheets(Sheets.Count) .Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = sColLabel2 .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = sColLabel1 .PlotArea.Select With Selection.Border .ColorIndex = 16 .Weight = xlThin .LineStyle = xlContinuous End With With Selection.Interior .ColorIndex = 2 .PatternColorIndex = 1 .Pattern = xlSolid End With With .Axes(xlCategory) .HasMajorGridlines = True .HasMinorGridlines = False End With With .Axes(xlValue) .HasMajorGridlines = True .HasMinorGridlines = False End With .SeriesCollection(1).Trendlines.Add(Type:=xlLinear , Forward _ :=0, Backward:=0, DisplayEquation:=True, DisplayRSquared:=True).Select .PlotArea.Select End With Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
scatter chart x axis problem | Charts and Charting in Excel | |||
Pivot Table, Line Chart with Secondary Y axis, Page Field selection destroys Y2 axis | Charts and Charting in Excel | |||
X axis label problem | Charts and Charting in Excel | |||
can't change how data on 2nd axis is being displayed | Charts and Charting in Excel | |||
Problem with date base units for x axis | Charts and Charting in Excel |