Thread: Trouble automating creation of charts/graphs

Hi,

I'm a newb at VBA and am having a little trouble finding help on what I'm trying to code. Would appreciate any help anyone can provide.

Basically, I am writing a macro to automate the process of creating multiple graphs. I plan to write some code (later)which will output a list of row numbers. I need this code to be able to loop through that list and create a graph for each of those rows.

I tried to define variables for the data range so that I can loop through and just change the variable, but I don't think I am doing it correctly. Any advice?

  
Sub LoudnessChart() 
Dim LChart As ChartObject 
Dim Lrange As Range 
Dim Lrow As String 
Dim Lstr As String 
Dim Lname As String 
  
Lrow = 170 
Lstr = "Range(" & Chr(34) & "Summary!G" & Lrow & ":V" & Lrow & Chr(34) & ").Values" 
Set Lrange = Lstr 
  
  
 Windows("Sorted.xls").Activate 
    Sheets("Temp").Select 
    Set LChart = ActiveSheet.ChartObjects.Add _ 
        (Left:=100, Width:=375, Top:=75, Height:=225) 
 ' 
   LChart.Chart.SetSourceData Source:=Lrange 
    LChart.Chart.ChartType = xlColumnClustered 
    LChart.Chart.SeriesCollection(1).XValues = "=Summary!R3C7:R4C22" 
  
Lname = Chr(34) & "=Summary!R" & Lrow & "C3" & Chr(34) 
  
    LChart.Chart.SeriesCollection(1).Name = Lname 
       With Schart.Chart 
        .HasTitle = True 
        .ChartTitle.Characters.Text = "Power Door Lock Power  Lock Peak Sharpness" 
        .Axes(xlCategory).HasTitle = False 
        .Axes(xlCategory).TickLabelSpacing = 1 
        .Axes(xlCategory).TickLabels.Orientation = xlUpward 
        .Axes(xlValue).HasTitle = True 
        .Axes(xlValue).AxisTitle.Characters.Text = "Acum" 
        .HasLegend = False 
    End With 
End Sub

As a matter of fact I don't have any programming experience. Got thrown into an assignment and am using these forums and google as my guide :/ So thank you very much for responding. I appreciate you taking the time to look at my code.

I was able to figure out the graphing sub-routine (amazed that it worked!), and am now just attempting the clean up the code. Could you please tell me how I can simplify the lines in my code which activate or select worksheets? I have included a small function which does this....

Thanks in advance for any guidance!

Function ARLSearch(ARLNumber As Integer)
Dim Index As Integer
'Finds input ARL number in Column 4 outputs row number to appropriate graphing sub macro

Index = 5
    
    While (Index < 500)
        Windows("Sorted OSQ HC 031910.xls").Activate
        Sheets("ARLSummary").Select
        If Cells(Index, 4).Value = ARLNumber Then
        'If Index is an even number go to LoudnessChart subroutine
        LoudnessChart (Index)
        'If Index is an odd number go to SharpnessChart subroutine
        'SharpnessChart (Index)
        End If
        Index = Index + 1
    Wend

End Function