CODEWITHSUNDEEP
vbaexcelgraph
A Cohen
A Cohen

Reputation: 456

VBA - Incorrect graphing code

First of all, thank you for all the help in the past, this site is a blessing, and you all are saints. Now down to business

I am having trouble with one set of my test data, the other graphs correctly, however, this set of data is causing my graphs to look wrong. I think I found the problem, but I am unsure on how to fix it. When my code creates the range to graph, it is not grabbing the right range due to a mathematical error. For reference, DataLength = 102, and this is what the graph and a portion of the data looks like.graph I need to graph to "CZ" but it is only going to D because fRemainder = 0 And here is the out of wack code. 

    Dim iAlpha As Integer, fAlpha As Integer
    Dim iRemainder As Integer, fRemainder As Integer
    Dim ConvertToLetter As String
    Dim fConvertToLetter As String

    iAlpha = Int((DataLength) / 26) '26 for the letters
    fAlpha = Int((DataLength + 2) / 26) 'for the average and sd functions, since they start at C not A
    iRemainder = DataLength - (iAlpha * 26)
    fRemainder = DataLength + 2 - (fAlpha * 26)
    If iAlpha > 0 Then
       ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
    If fAlpha > 0 Then
       fConvertToLetter = Chr(fAlpha + 64)
    End If
    If fRemainder > 0 Then
       fConvertToLetter = fConvertToLetter & Chr(fRemainder + 64)
    End If

And for additional reference, this is the actual graphing code is. The first line offsets from "C13", however the cell changes with each grouping, ie. "C13" "C49" "C85" etc. 

ActiveCell.Offset(-7, -2).Select
ActiveCell.Range("A1:" & fConvertToLetter & "4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Activate
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
    .ChartType = xlLine
    .Axes(xlCategory).Select
    .HasTitle = True
    .ChartTitle.Text = Range("B" & (12 * x - 5)).Value
End With
With ActiveChart.Parent
     .Top = 153 * x + 12.75 * 2 ' reposition cells are 12.75 high and there are 36 cells between
     .Left = 50   ' reposition
End With

Upvotes: 0

Views: 260

Answers (1)

Tony M
Tony M

Reputation: 1762

There are different ways to define the ranges used for charts. In the example below, I show 2 ways, one to make the initial chart, and then another based upon adding a new series to the chart. It is best to remove statements like selection and .select that the macro recorder uses, and instead define range variables. These range variables can be fine-tuned using the .offset function as needed. Step through the code as I show in the animated .gif to help understand how it works, and then adjust as needed for your application.

enter image description here

Option Explicit
Sub chartRange()
Dim r As Range, chObj As ChartObject, ser As Series
Set r = ActiveSheet.Range("A1:B5")
Set chObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    With chObj
    .chart.ChartType = xlXYScatterLines
    .chart.SetSourceData Source:=r
    End With
    If MsgBox("Plot y2 also?", vbYesNo) Then
      Set ser = chObj.chart.SeriesCollection.NewSeries
      Set r = ActiveSheet.Range("A1:A5")
      ser.XValues = r
      ser.Values = r.Offset(0, 2)
      ser.Name = "y2"
    End If
End Sub

Upvotes: 2

Related Questions