Extending a range horizontally for chart using VBA

[mvervair]

I'm doing a horizontal range but having issues. Here's my example code:

    resSurvey = Range("C3").End(xlToRight).Column
    Sheets("dash").ChartObjects("Chart 12").Activate
    ActiveChart.SetSourceData Source:=Range("res_surveys!$C$35:$" & resSurvey & "$35")

This doesn't work because my variable is a number. I'm trying to piece together how to use the Cells object inside the range object when the cells should refer to a different worksheet than the one my chart is on. Any advice, insight or helping me (and others) understand concepts I'm missing help!

 

[pgc01]

Hi

If I understood correctly, try:

Sub Test()
Dim cht As Chart
Dim resSurvey As Long
    
    Set cht = Sheets("dash").ChartObjects("Chart 12").Chart
    
    With Worksheets("res_surveys")
        resSurvey = .Range("C3").End(xlToRight).Column
        cht.SetSourceData Source:=.Range(.Cells(35, "C"), .Cells(35, resSurvey))
    End With

End Sub

 

[mvervair]

That's it, thank you kind stranger!

Out of curiosity, is there a meaningful difference in:

[COLOR=#333333]        cht.SetSourceData Source:=.Range(.Cells(35, "C"), .Cells(35, resSurvey))[/COLOR]

vs. not placing the "." before the Range

[COLOR=#333333]        cht.SetSourceData Source:=Range(.Cells(35, "C"), .Cells(35, resSurvey))[/COLOR]

I ask because the code seemed to work both ways whether it was .Range or Range.

Thank you again!

 

[pgc01]

Hi

I'm glad it helped.


About the Range() - .Range()

I qualify the Range object to make it clear that it is a range in the res_surveys worksheet (specified in the With block).

In this case you are right that both work because I have also qualified both the Cell()'s and so there is no doubt that we are referring to the res_surveys worksheet, but ...

that will not always be the case.

This is an example: let's say you want to specify the start range directly with the address like:

1 - Range qualified

        cht.SetSourceData Source:=.Range("C35", .Cells(35, resSurvey))

In this case it will work independently of which is the active sheet at the moment of execution. The .Range makes sure that we know that the "C35" refers to res_surveys.


2 - Range not qualified

        cht.SetSourceData Source:=Range("C35", .Cells(35, resSurvey))

In this case, however, sometimes it may work and sometime it won't.

Let's say that when you run the code the active worksheet is Sheet1 and the column number in the variable resSurvey is 5 (column "E").

The code sees "Range("C35", ..." and it will assume that you are talking about Sheet1!C35 because Sheet1 is the active worksheet. Then it sees "..., .Cells(35, resSurvey)" and, because Cell() is qualified it knows it refers to the worksheet res_surveys.

It's like you are specifying the range Sheet1!C35:res_surveys!E35 which makes no sense.

In this case the code will fail.

This code will only work if, at the moment of execution, the active worksheet is res_surveys.


So, in conclusion, you are right, in this case you may use both. I prefer, however, to always qualify the Range() making it clear that I'm working with the worksheet specified in the With block. It will work no matter how I specify the start and end range.

 

[mvervair]

Very clear, thanks again. This will allow me to clean up a lot of my charts' code now since I can qualify their data sources and stop jumping around between sheets.