Positioning charts using VBA

[richanor]

Hi all

I might be getting a bit out of my league here but here goes:

I have a workbook which I drop my raw sales data into every month. It reformats the data, and converts it into a pivot table, then pivots the data around 3 different ways and produces 3 static charts (i.e. not pivot charts) from the data.

This is where I am getting a bit lazy. The charts it produces are just dumped on the worksheet any old way. I am trying to get them to line up in a specific way. So I have botched together this code which highlights the range I would like the chart to cover:

Sub FindRange()

    Range("A1").Select
      
    Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Range(Selection, Selection.End(xlToRight)(1, 5)).Select
    Range(Selection, Selection.End(xlDown)(1)).Select
    Selection.Offset(0, 5).Select

End Sub

I now have highlighted an area of the workbook where I would like to place my first chart. I have also taken a peice of code from John Peltiers site:

     Sub CoverRangeWithAChart()
         Dim RngToCover As Range
         Dim ChtOb As ChartObject
         Set RngToCover = ActiveSheet.Range("D5:J19")
         Set ChtOb = ActiveChart.Parent
         ChtOb.Height = RngToCover.Height ' resize
         ChtOb.Width = RngToCover.Width   ' resize
         ChtOb.Top = RngToCover.Top       ' reposition
         ChtOb.Left = RngToCover.Left     ' reposition
     End Sub

which defines a range to place a chart in. What I would like to do (and I don't know if this is possible or not), is to merge the two, so that the range in John Peltiers code is defined by my first peice of code.

I hope it's clear what I am trying to do! Firstly - is this possible? If so can anyone help me out a bit?

Many thanks

Rich
[/code]

 

[Jon Peltier]

Try this. It's a little inelegant because of all of the selection changes, but it does the trick.

Sub CoverRangeWithAChart()
    Dim RngToCover As Range 
    Dim ChtOb As ChartObject 

    Range("A1").Select 
      
    Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False).Activate 
    Range(Selection, Selection.End(xlToRight)(1, 5)).Select 
    Range(Selection, Selection.End(xlDown)(1)).Select 
    Selection.Offset(0, 5).Select 

    Set RngToCover = Selection

    Set ChtOb = ActiveChart.Parent 
    ChtOb.Height = RngToCover.Height ' resize 
    ChtOb.Width = RngToCover.Width   ' resize 
    ChtOb.Top = RngToCover.Top       ' reposition 
    ChtOb.Left = RngToCover.Left     ' reposition 

End Sub

 

[richanor]

Hi Jon

Thanks a lot for your help! I tried the code you posted, but it returned an error on the line:

Set ChtOb = ActiveChart.Parent

which said:

Run-time error '91':
Object variable or With block variable not set

Do you know what would be causing this?

Many thanks

Rich

 

[Jon Peltier]

Oh right, because the range is selected. Duh.

Assuming there's only the one chart on the worksheet:

Sub CoverRangeWithAChart() 
    Dim RngToCover As Range 
    Dim ChtOb As ChartObject 

    Range("A1").Select 
      
    Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ 
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ 
        False).Activate 
    Range(Selection, Selection.End(xlToRight)(1, 5)).Select 
    Range(Selection, Selection.End(xlDown)(1)).Select 
    Selection.Offset(0, 5).Select 

    Set RngToCover = Selection 

    Set ChtOb = ActiveSheet.ChartObjects(1)
    ChtOb.Height = RngToCover.Height ' resize 
    ChtOb.Width = RngToCover.Width   ' resize 
    ChtOb.Top = RngToCover.Top       ' reposition 
    ChtOb.Left = RngToCover.Left     ' reposition 

End Sub

 

[richanor]

Hi Jon

Thanks again for your help - that code works fine. Unfortunately I probably haven't explained what I was trying to do clearly enough - and as I have no idea how to tweak your code, I'm going to be cheeky and ask you (or anyone else !) to have another look at it?

What I have so far is a workbook into which I can dump my raw data every month, and it creates a pivot chart from the data, pivots the data in a particular way, selects the data, copies it, and pastes it (values only) into a seperate worksheet (cash pivot). It then pivots the data another two times and pastes the values onto the 'cash pivot' sheet underneath the first.

So I now have a worksheet (cash pivot) with 3 sets of static data, lets say in ranges B9:F31, B33:F55, and B57:F79 (although this will change every month)

The code I have been asking about in this post now selects the first data range, and creates a bar chart from it, then selects the second data range and creates a second barchart from it, and then a third from the third data set. I had been asking if there was a way I could merge some of your code with mine, so that when it selects my first data range, and creates the first chart - instead of dumping the chart anywhere, it drops it neatly into a defined range (which happens to be along side the data range).

Sorry about the laborious explanation! The code you have given me, seems to make the graph itelf, but it somehow makes one graph of all the data on the page (i.e. so I end up with 3 data ranges, but only 1 bar chart which diplays all of the data).

Would it be fairly simple to amend this to acheive my intended goal?

The code I am now working with is:

Sub MakeCharts()
'
'
    Dim MyChart As Object
    Range("A1").Select
    Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Range(Selection, Selection.End(xlToRight)(1, 0)).Select
    Range(Selection, Selection.End(xlDown)(0)).Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    Selection.AutoScaleFont = False
    Set MyChart = ActiveChart.Location(Where:=xlLocationAsObject, Name:="pivot cash")
    ActiveWindow.Visible = False
    With MyChart
    .Parent.Name = "TestChart"
    .HasTitle = False
    .Axes(xlCategory, xlPrimary).HasTitle = False
    .Axes(xlValue, xlPrimary).HasTitle = False
    End With
    

    Dim RngToCover As Range
    Dim ChtOb As ChartObject

    Range("A1").Select
      
    Cells.Find(What:="area", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False).Activate
    Range(Selection, Selection.End(xlToRight)(1, 4)).Select
    Range(Selection, Selection.End(xlDown)(2)).Select
    Selection.Offset(-1, 6).Select

    Set RngToCover = Selection

    Set ChtOb = ActiveSheet.ChartObjects(1)
    ChtOb.Height = RngToCover.Height ' resize
    ChtOb.Width = RngToCover.Width   ' resize
    ChtOb.Top = RngToCover.Top       ' reposition
    ChtOb.Left = RngToCover.Left     ' reposition

End Sub

 

[richanor]

Oh right, because the range is selected. Duh.

Assuming there's only the one chart on the worksheet:

Why does this code only work if there is only one chart on the worksheet? Is it easy to amend if you have 3 charts? i.e. can you place one chart, then re-define the range, then place another etc?

Sorry if this is a dumb question - I'm trying to figure out what the code is doing!

Rich

 

[Jon Peltier]

The code refers only to one chart because of this line:

  Set ChtOb = ActiveSheet.ChartObjects(1)

You could do all of this in a loop or however, in which you work on one range, then work on the corresponding chart, then work on the next range and on the next chart, etc.

It would be a good idea to explicitly name each chart, so you don't have to rely on the charts having the indexes you think they have. You named a chart here:

  .Parent.Name = "TestChart"

Simply name the charts "TestChart" & CStr(iLoop), where iLoop is the counter variable controlling which iteration you are performing.

 

[richanor]

Jon - you are a genius!

Thank you so much for your help (and persistance) with this. It's working like a dream, and will make my data analysis *much* easier from now on!

PS is it just me or does anyone else find it quite funny when you get a reply to your (seemingly complicated) question, and the reply is prefixed with "simply"

Simply name the charts "TestChart" & CStr(iLoop), where iLoop is the counter variable controlling which iteration you are performing.

Thanks again

Rich

 

[Jon Peltier]

Everything's simple, when you know how.