Macro to set Chart Size to a Range with variables

[jdog79]

Hello, I'm trying to get my graph into a specified range. I'm trying to make the range for the graph dependent on the dimensions of the table next to it (for uniformity and neatness). I'm getting an error on how I defined my range. Current values of F and T are 0 and 6 respectively. Also, I found this code online that uses a loop, how do I set the chart dimensions for the active chart only?

Set rng = ActiveSheet.Range(Range("A13").Offset(F, 0), Range("A14").Offest(8 + T, 7))
    For x = 1 To ActiveSheet.Shapes.Count
        If ActiveSheet.Shapes(x).Type = msoChart Then
            ActiveSheet.Shapes(x).Width = rng.Width
            ActiveSheet.Shapes(x).Height = rng.Height
            ActiveSheet.Shapes(x).Top = rng.Top
            ActiveSheet.Shapes(x).Left = rng.Left
        End If
    Next

Also here: http://www.ozgrid.com/forum/showthread.php?p=430537#

 

[tusharm]

What error?

 

[Andrew Poulsom]

For the second Offset you have Offest.

 

[jdog79]

well it's amazing what a fresh pair of eyeballs can do for you! They typo fixes my issue with the range, however I'm still unsure how how set the dimensions for just the active chart, without looping through all charts.

 

[tusharm]

Untested...

with activechart.parent
.left=rng.left
.top=rng.top
    '...whatever else you want to set
    end with

well it's amazing what a fresh pair of eyeballs can do for you! They typo fixes my issue with the range, however I'm still unsure how how set the dimensions for just the active chart, without looping through all charts.

 

[jdog79]

This worked:

If Not ActiveChart Is Nothing Then 
    With ActiveChart.Parent 
        .Width = rng.Width 
        .Height = rng.Height 
        .Top = rng.Top 
        .Left = rng.Left 
    End With 
End If

glad you liked all my typos, I think VBA has cooked my brain!

 

[tusharm]

Those kinds of errors should not happen. Enable and use Intellisense (in the VB Editor, select Tools | Options... | Editor tab | check 'Auto List Members' and...heck, just check every checkbox in that section).

This worked:
{snip}

glad you liked all my typos, I think VBA has cooked my brain!