0

The macro below was created to update images used in a bubble chart. Sheet "NTA Chart" contains the bubble chart and sheet "Pie Charts" contains the pie charts that are used as images over the bubbles. Distributor is name of the data points, and one pie relates to one data point.

I am now receiving error "Run-time error -2147417848 (80010108) - Method 'Offset' of object 'Range' failed." When I try to debug the line "strDistributor = chtTemp.TopLeftCell.Offset (-1)" is highlighted. This was designed in Excel 2003, but I am now using Excel 2010.

Option Explicit

Sub MakePieMarker()

    Dim chtMain As Chart
    Dim chtTemp As ChartObject
    Dim strDistributor As String
    Dim lngSeries As Long

    Set chtMain = ActiveWorkbook.Charts("NTA Chart")

    With ActiveWorkbook.Worksheets("Pie Charts")
        For Each chtTemp In .ChartObjects
            strDistributor = chtTemp.TopLeftCell.Offset(-1)
            lngSeries = CLng(Mid(strDistributor, 12))
            chtTemp.CopyPicture xlScreen, xlPicture
            chtMain.SeriesCollection(lngSeries).Paste
        Next
    End With


End Sub
Community
  • 1
  • 1
Jill
  • 1
  • 1

1 Answers1

0

It seems this is some kind of a bug. The workaround is to change that line to:

strDistributor = Range(chtTemp.TopLeftCell.Address).Offset(-1).Address

or this, depending on what string outcome you want:

strDistributor = Range(chtTemp.TopLeftCell.Address).Offset(-1).value

Hope it helps.

ZygD
  • 22,092
  • 39
  • 79
  • 102
  • Thanks. That appeared to help on that error, but now I get a run-time error '13': Type mismatch. It doesn't like the code line - lngSeries = CLng(Mid(strDistributor, 12)) – Jill Feb 08 '15 at 21:00
  • Still getting the run-time error 13 - type mismatch. – Jill Feb 08 '15 at 21:03
  • I see `Clng` in the following code. It means that in that in this (`Range(chtTemp.TopLeftCell.Address).Offset(-1).Address`) cell there should be numbers. Can you check it? – ZygD Feb 08 '15 at 21:08
  • Not sure how to find the numbers. In TopLeftCell it shows $C$23 and Value = 0 – Jill Feb 08 '15 at 21:16
  • Ok, so `Offset(-1)` indicates that you should look at $C$22. There you should have at least 12 symbols length string with number(s) beginning from at least the 12th symbol. The following line (`lngSeries = CLng(Mid(strDistributor, 12))` implies that it should get a string of this length passed to it in order not to throw a type mismatch error. – ZygD Feb 08 '15 at 21:32
  • As I understand in $C$22 cell, at the very end there should be just one digit (maybe "1"?). Also, I am surprised that after transitioning to Excel 2010 you did not receive an error in one of your first lines (`Set chtMain = ActiveWorkbook.Charts("NTA Chart")`). Because I had to change that line to `Set chtMain = ActiveWorkbook.ActiveSheet.ChartObjects("NTA Chart").Chart` – ZygD Feb 08 '15 at 22:01
  • Was the answer helpful? – ZygD Mar 14 '15 at 21:58