Consulting

Results 1 to 4 of 4

Thread: Charting from Range assigned using Cells method!?

  1. #1
    VBAX Newbie
    Joined
    Dec 2011
    Posts
    2
    Location

    Charting from Range assigned using Cells method!?

    Hi all, I've been literally for a few hours and can't figure this out with multiple multiple google searches.

    I'm trying to create multiple graphs from vba. Each graph will potentially have different ranges and I'm running the "create graph function" in a loop with other data. I need to define the range in which the different graphs select their data from within these loops. That's why I can't define it like this, for example:

    ActiveChart.SetSourceData Source:=Sheets("Ignore This").Range("A3 : D4"), PlotBy:=xlRows

    I want to define them like this:

    ActiveChart.SetSourceData Source:=Sheets("Ignore This").Range(Cells(3, 2), Cells(4, 13)), PlotBy:=xlRows

    where the numbers would be replaced by variables which are looped using for/next.

    I'll post the code if necessary but I think it is merely syntax in nature and therefore I just need to know how to select a range using cells by number!

    Thanks in advance!

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Maybe

    [vba]

    With Sheets("Ignore This")

    ActiveChart.SetSourceData Source:=.Range(.Cells(3, 2), .Cells(4, 13)), PlotBy:=xlRows
    End With[/vba]
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Newbie
    Joined
    Dec 2011
    Posts
    2
    Location
    You sir, are god.

    Thanks so much!

    I'd still like to know why that worked and what I was doing didn't. Why was the "with" loop necessary to make it work properly?

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    It wasn't the With per se, but the fully qualifying the Cells object to the same sheet.

    I could have written it as

    [vba]
    ActiveChart.SetSourceData Source:=Sheets("Ignore This") _
    .Range(Sheets("Ignore This").Cells(3, 2), Sheets("Ignore This").Cells(4, 13)), PlotBy:=xlRows
    [/vba]

    but I used With ... End With to simplify and make the code more readable.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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