Multiple Non Adjacent Ranges For Chart

  • Hi,


    I am generating a chart through a macro. I am using 4 non adjacent range and the end point of the range is represented by a variable as the end point is not fixed. I am using following code.





    Can any one please help to rectify the error. Your help is highly appreciated.


    Regards


    Angshuman

  • Re: Select Multiple Non Adjacent Range For Chart With Variables


    Code
    Source:= Sheets("Summary").Range( _ 
    "StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4")


    Should probably be:


    Code
    Source:= Sheets("Summary").Range( _ 
    StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)


    You can't put your variables in the quotes or VB treats them as literals. You have to concatenqate them with the ampersand symbol.


    hth,


    Mav

  • Re: Select Multiple Non Adjacent Range For Chart With Variables


    Hi ,


    I changed as per your advice and it worked. however there are two more lines in my code which are as below and needs some changes.


    Code
    ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _
        StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4)
    
    
    ActiveChart.SeriesCollection(4).Name = "=Summary!R17C7"
    ActiveChart.SeriesCollection(4).XValues = "=Summary!R19C2:R23C2"


    In the above code I need to change the range of the SeriesCollection(4).Xvalues with variables.


    Please help


    Thanks and Regards


    Angshuman

  • Re: Select Multiple Non Adjacent Range For Chart With Variables


    Try changing:

    Code
    ActiveChart.SetSourceData Source:=Sheets("Summary").Range( _ 
    "StartPoint1:EndPoint1,StartPoint2:EndPoint2,StartPoint3:EndPoint3,StartPoint4:EndPoint4"), PlotBy:=xlColumns

    to

    Code
    ActiveChart.SetSourceData Source:=Sheets("Summary").Range(StartPoint1 & ":" & EndPoint1 & "," & StartPoint2 & ":" & EndPoint2 & "," & StartPoint3 & ":" & EndPoint3 & "," & StartPoint4 & ":" & EndPoint4), PlotBy:=xlColumns

    or reduce the number of '&'s in the final line with

    but it may be easier to:

    Code
    Sub blah2()
    myRow = 5
    With Sheets("Summary")
     Set myRng1 = .Range("C19").Resize(myRow)
     Set myRng2 = .Range("E19").Resize(myRow)
     Set myRng3 = .Range("G19").Resize(myRow)
     Set myRng4 = .Range("I19").Resize(myRow)
    End With
    ActiveChart.SetSourceData Source:=Union(myRng1, myRng2, myRng3, myRng4), PlotBy:=xlColumns
    End Sub

    p45cal
    ps not fully tested (all except for setting up a chart (the ranges have been verified))
    pps I changed Row to myRow since 'Row' is a reserved word (or some such!).

  • Re: Select Multiple Non Adjacent Range For Chart With Variables


    What macro gives the error, and which line (if applicable)?

  • Re: Select Multiple Non Adjacent Range For Chart With Variables


    Hi,


    Following are the codes I am using now. The code i working fine for the first graph. however for the next graph it gives error "Run -time error '1004'; Application-definded or Object-defined error"..Please help.



    Thanks and Regards,


    Angshuman

  • Re: Multiple Non Adjacent Ranges For Chart


    Hi Andy,


    As per my understanding, it is because when I the second chart is being created, it is also taking the old data range. Can you please tell me the code with whcihc I can delete the old data range that was used for the first chart.


    Thanks and Regards


    Angshuman

  • Re: Multiple Non Adjacent Ranges For Chart


    Not the case in my test. Each chart is based on different non adjacent ranges.


    I think a test dataset would help. At least I would be able to generate the problem you see.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!