Solved

Posted on 2011-05-10

The code below works perfectly in Excel 2003, but in 2007 it triggers a "Method "Offset' of object 'Range' failed" error on this line: .MinimumScale = chr.TopLeftCell.Offset(0, -1) The top left cells all have a valid number in them.

Can you see anything in the code that would explain the error?

Thanks,

John

Can you see anything in the code that would explain the error?

Thanks,

John

```
Sub autoScaleGeneral()
Range("A6:A200").Calculate
[ChartMin].Calculate
[MajorUnit].Calculate
Dim chr As ChartObject
For Each chr In ActiveSheet.ChartObjects
Set rng1 = Intersect(ActiveSheet.[ChartRange1], chr.TopLeftCell)
Set rng2 = Intersect(ActiveSheet.[ChartRange2], chr.TopLeftCell)
If Not rng1 Is Nothing Then
chr.Activate
With ActiveChart.Axes(xlValue)
'If chr.TopLeftCell.Offset(0, -1) < 1 Then
.MinimumScale = chr.TopLeftCell.Offset(0, -1)
.MaximumScale = 1
.MajorUnit = chr.TopLeftCell.Offset(1, -1)
.CrossesAt = cminimumscale
If ActiveSheet.[ChartMin] > 0.98 Then
ActiveSheet.[ChartMin].NumberFormat = "#.#"
Else
End If
End With
End If
If Not rng2 Is Nothing Then
chr.Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1 - [ChartMin]
.MajorUnit = [MajorUnit]
.CrossesAt = 0
End With
End If
Next
[A1].Select
End Sub
```

15 Comments

I've got Excel 2010, and it is having a conniption fit trying to offset the top left cell of the chart object. Here is my workaround--without all those nasty .Activate statements. I needed to use the Cells collection and reference the .Column and .Row properties of chr.TopLeftCell to get your desired references.

```
Sub autoScaleGeneral()
Dim rg As Range, rng1 As Range, rng2 As Range
Dim cminimumscale As Double
Range("A6:A200").Calculate
[ChartMin].Calculate
[MajorUnit].Calculate
Dim chr As ChartObject
For Each chr In ActiveSheet.ChartObjects
Set rg = chr.TopLeftCell
Set rng1 = Intersect(ActiveSheet.[ChartRange1], rg)
Set rng2 = Intersect(ActiveSheet.[ChartRange2], rg)
If Not rng1 Is Nothing Then
With chr.Chart.Axes(xlValue)
'If cells(rg.row,rg.column-1) < 1 Then
.MinimumScale = Cells(rg.Row, rg.Column - 1)
.MaximumScale = 1
.MajorUnit = Cells(rg.Row + 1, rg.Column - 1)
.CrossesAt = cminimumscale
If ActiveSheet.[ChartMin] > 0.98 Then
ActiveSheet.[ChartMin].NumberFormat = "#.#"
Else
End If
End With
End If
If Not rng2 Is Nothing Then
With chr.Chart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1 - [ChartMin]
.MajorUnit = [MajorUnit]
.CrossesAt = 0
End With
End If
Next
End Sub
```

Brad

I've uploaded it in 2003 and 2007 format. I continue to get the aforementioned error. Obviously I hope you do as well, or else I just wasted the last two hours! :-)

Thanks,

John

RangeError2-2003.xls

RangeError2.xlsm

Apparently, this is a pretty widely reported bug in Excel 2007 and 2010.

Jon Peltier talks about it here: http://peltiertech.com/Wor

Brad

I patched the macro as shown below, and it now runs in Excel 2003, 2007 and 2010.

Brad

```
Sub autoScaleGeneral()
Range("A6:A200").Calculate
Dim rg As Range
[ChartMin].Calculate
[MajorUnit].Calculate
Dim chr As ChartObject
For Each chr In ActiveSheet.ChartObjects
Set rg = chr.TopLeftCell
Set rng1 = Intersect(ActiveSheet.[ChartRange1], rg)
Set rng2 = Intersect(ActiveSheet.[ChartRange2], rg)
If Not rng1 Is Nothing Then
With chr.Chart.Axes(xlValue)
'If chr.TopLeftCell.Offset(0, -1) < 1 Then
.MinimumScale = Cells(rg.Row, rg.Column - 1)
.MaximumScale = 1
.MajorUnit = Cells(rg.Row + 1, rg.Column - 1)
.CrossesAt = cminimumscale
If ActiveSheet.[ChartMin] > 0.98 Then
ActiveSheet.[ChartMin].NumberFormat = "#.#"
Else
End If
End With
End If
If Not rng2 Is Nothing Then
With chr.Chart.Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 1 - [ChartMin]
.MajorUnit = [MajorUnit]
.CrossesAt = 0
End With
End If
Next
End Sub
```

RangeError2Q27030007.xlsm
By clicking you are agreeing to Experts Exchange's Terms of Use.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**17** Experts available now in Live!