none
Question on Chart with VBA for excel RRS feed

  • Question

  • I will have an excel file worksheet with 4 columns A-D. Columns A & B go from row 1 to M, while columns C & D go from row 1 to N. The values of M & N can vary from file to file. How to write a general VBA code so it will plot B and D on the Y-axis and A & C on Y (represents same scale) as a scatter line or line plot? I am using excel2003.
    • Moved by kleinma Wednesday, July 20, 2011 3:28 AM VBA question (From:Visual Basic General)
    Wednesday, July 20, 2011 1:49 AM

All replies

  • To start, record a macro of you creating the graph manually. You then just need to add code to adjust data source to fit data.

    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Wednesday, July 20, 2011 4:50 AM
  • When I record, source comes as static values of M and N for the specific example. As I mentioned, M & N can change file to file. My problem is how can I write a code that can programmaticaly find & use M & N. Otherwise, if I have to do manual change from file to file, then no purpose of VBA script for this case. 

    Below is how my macro came out. So in this sample M was 5 and N was 6. In another file M could 1000 and N could 995. How am I to dynamically adjust below lines that has R5C and R6C?

    Sub Macro2()

        Charts.Add

        ActiveChart.ChartType = xlXYScatterLines

        ActiveChart.SetSourceData Source:=Sheets("data").Range("A1:D6"), PlotBy:= _

            xlColumns

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection.NewSeries

        ActiveChart.SeriesCollection.NewSeries

        ActiveChart.SeriesCollection(1).XValues = "=data!R1C1:R5C1"

        ActiveChart.SeriesCollection(1).Values = "=data!R1C2:R5C2"

        ActiveChart.SeriesCollection(1).Name = "=""s1"""

        ActiveChart.SeriesCollection(2).XValues = "=data!R1C3:R6C3"

        ActiveChart.SeriesCollection(2).Values = "=data!R1C4:R6C4"

        ActiveChart.SeriesCollection(2).Name = "=""s2"""

        ActiveChart.Location Where:=xlLocationAsNewSheet

        With ActiveChart

            .HasTitle = False

            .Axes(xlCategory, xlPrimary).HasTitle = False

            .Axes(xlValue, xlPrimary).HasTitle = False

        End With

     

    End Sub

    Wednesday, July 20, 2011 11:08 PM
  • If your columns are fixed, then all you need to calculate is M and N.  Does the data run out after you reach rows M and N?  If so, you can just loop through the rows until the cells no longer have values.
    Saturday, July 23, 2011 7:58 AM
  • When I record, source comes as static values of M and N for the specific example. As I mentioned, M & N can change file to file. My problem is how can I write a code that can programmaticaly find & use M & N. Otherwise, if I have to do manual change from file to file, then no purpose of VBA script for this case. 

    Below is how my macro came out. So in this sample M was 5 and N was 6. In another file M could 1000 and N could 995. How am I to dynamically adjust below lines that has R5C and R6C?

     

    Sub Macro2()

        Charts.Add

        ActiveChart.ChartType = xlXYScatterLines

        ActiveChart.SetSourceData Source:=Sheets("data").Range("A1:D6"), PlotBy:= _

            xlColumns

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection(1).Delete

        ActiveChart.SeriesCollection.NewSeries

        ActiveChart.SeriesCollection.NewSeries

        ActiveChart.SeriesCollection(1).XValues = "=data!R1C1:R5C1"

        ActiveChart.SeriesCollection(1).Values = "=data!R1C2:R5C2"

        ActiveChart.SeriesCollection(1).Name = "=""s1"""

        ActiveChart.SeriesCollection(2).XValues = "=data!R1C3:R6C3"

        ActiveChart.SeriesCollection(2).Values = "=data!R1C4:R6C4"

        ActiveChart.SeriesCollection(2).Name = "=""s2"""

        ActiveChart.Location Where:=xlLocationAsNewSheet

        With ActiveChart

            .HasTitle = False

            .Axes(xlCategory, xlPrimary).HasTitle = False

            .Axes(xlValue, xlPrimary).HasTitle = False

        End With

     

    End Sub

     


    You are almost done.  If you were doing this by hand how would you determine M and N?
    Tushar Mehta (Technology and Operations Consulting)
    www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)
    Microsoft MVP Excel 2000-Present
    Saturday, July 23, 2011 5:48 PM
  • What do you mean by rows 1 to M? Is M a column (which isn't a row) or a variable?

    If data is contiguous (no empty cells) then Data from A1 to A10 can be found with:

    = "=data!R1C1:R"& range("A1").End(xlDown).row &"C1"


    Rod Gill

    The one and only Project VBA Book Rod Gill Project Management
    Sunday, July 24, 2011 4:48 AM