Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Graphing variable data

  1. #1
    New Member
    Join Date
    May 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am creating a bar stacked bar chart. I send over data from access and open up excel thru automation. I have created a macro that creates this chart ... however,

    the macro HARDCODES the initial data range. When I send over new data (with more rows), the macro, of course, only selects the previous datarange I sent.

    Can anyone help me with code that I could use that would allow for a variable data range to be input into the "SETSOURCEDATA" line of my macro?

    Thanks!!
    (e.g. data)

    Indication Offset Duration
    Ind_1 2 5
    Ind_2 4 5
    Ind_3 6 9
    (Above cells DATARANGE a1:c3

    Indication Offset Duration
    Ind_1 2 5
    Ind_2 4 5
    Ind_3 6 9
    Ind_4 7 2
    Ind_5 8 7
    Ind_6 9 4
    (When I send THIS data over, my macro still only uses the previous A1:C3 range!!!!


    THANKS!!!

    [ This Message was edited by: jamesgw on 2002-05-10 11:20 ]

    [ This Message was edited by: jamesgw on 2002-05-10 11:22 ]

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    N.E. Ohio
    Posts
    236
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    here is one I use - I have fomulas at j1 and k1 that find my range - you will need to change it to suit your needs of course

    Sub set_chart_range()
    '
    ' Macro3 Macro
    ' Macro recorded 01/11/2001 by Keith O'Brien
    '

    Dim chart_range1 As Integer
    Dim chart_range2 As Integer
    Dim range_name1 As String
    Dim range_name2 As String
    chart_range1 = Cells(1, 10).Value
    chart_range2 = Cells(1, 11).Value
    range_name1 = "A2,L2:M2,A" & chart_range1 & ":A" & chart_range2 & ",l" & chart_range1 & " :m" & chart_range2

    Sheets("Chart1").Select
    ActiveChart.ChartArea.Select
    ActiveChart.SetSourceData Source:=Sheets("yoursheet").Range( _
    range_name1), PlotBy:=xlColumns

    End Sub

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •