[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Runtime error that occurs in Excel 2007 but not 2003

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
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

Open in new window

0
gabrielPennyback
Asked:
gabrielPennyback
  • 7
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
What column is the top left cell in? If it's A that would be a problem.
0
 
gabrielPennybackAuthor Commented:
Yes it is Column A. What's the fix? :-)
0
 
Rory ArchibaldCommented:
Don't try and refer to a column to the left of column A... :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gabrielPennybackAuthor Commented:
I tied inserting a Column before A but I get the same error. Wishful thinking ...
0
 
Rory ArchibaldCommented:
I'd probably need to see a workbook to work out what is going wrong, if the topleftcell is not in column A
0
 
gabrielPennybackAuthor Commented:
Oh I see what you mean. No, the chart is in Column B. The scale number is in Column A.
0
 
Rory ArchibaldCommented:
Are you sure it hasn't moved in 2007?
0
 
gabrielPennybackAuthor Commented:
Yes. I'm trying to strip down the book to send.
0
 
byundtCommented:
John,
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

Open in new window


Brad
0
 
gabrielPennybackAuthor Commented:
Hi Rory, well it took me all this time to strip it out of its parent workbook and remove all the names with external links.  The error is triggered when you run 'SwitchViews' by clicking on the white diamond in B2. 'SwitchViews' calls 'autoScaleGeneral' which is where it bugs.

 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
0
 
byundtCommented:
John,
Apparently, this is a pretty widely reported bug in Excel 2007 and 2010.
Jon Peltier talks about it here: http://peltiertech.com/WordPress/connect-two-xy-series-with-arrows-2007-error/#comment-94904

Brad
0
 
byundtCommented:
John,
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

Open in new window

RangeError2Q27030007.xlsm
0
 
gabrielPennybackAuthor Commented:
Can't wait to try this in the morning!
0
 
gabrielPennybackAuthor Commented:
Amazing Brad, thanks. Not only does it work, but without any selecting! Any idea why Excel 2007 requires defining the money cells as 'Cells(x,y)' rather than 'cel.Offset(0, -1)' ? Or is it just an unintended consequence of the re-design?

In any event, thank you so much.

- John
0
 
byundtCommented:
John,
It's a known bug.

Jon Peltier proposed a different workaround using the .Address property of the .TopLeftCell to create a Range variable. Probably more ways than that to skin this cat.

Brad
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now