Application-defined or object-defined error...


New Member
Aug 5, 2014
Hi, I'm bored so I felt like making a pie chart of my cell phone data usage. I made a spreadsheet (table below) that has a column that labels each application and the corresponding data that it used. My macro then creates a second table of only the applications that used 2% or more of the total data for that month and creates a pie chart from the second set of data. Interestingly it works for the first sheet (which labels october to november) but not the second (november to december).

The error I'm getting is "application-defined or object-defined error" but I'm a little too new to this to know what's wrong. If it helps, it seems to error out before it can run the autofit line. If you know what I'm doing wrong or have any simplifications for my code, please let me know!

Thank you in advance!

Sub CellularDataExtractor()

Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets(ActiveSheet.Name)

prep = MsgBox("Is the active cell on the first label?", vbYesNo, "Just checkin'")

    If prep = vbNo Then
    MsgBox "Then fix it."
    Exit Sub
    End If

firstRow = ActiveCell.Row
j = firstRow
k = firstRow

textCol = ActiveCell.Column
newTextCol = textCol + 5

gbCol = textCol + 1
newGbCol = gbCol + 5

Do While Cells((k + 1), 1).Value <> ""      'finds the row with the last label
    k = k + 1

lastRow = k

tolerance = Application.Sum(Range(Cells(firstRow, gbCol), Cells(lastRow, gbCol))) * 0.02     'picks labels with at least 2% influence

For i = firstRow To lastRow

   If ws1.Cells(i, gbCol).Value > tolerance Then

       ws1.Cells(j, gbCol).Copy ws1.Cells(j, newGbCol)     'is here to copy the format of the gb row
       ws1.Cells(j, newTextCol).Value = ws1.Cells(i, textCol).Value
       ws1.Cells(j, newGbCol).Value = ws1.Cells(i, gbCol).Value
       j = j + 1

   End If

Next i


With ActiveSheet.ChartObjects.Add(Left:=500, Width:=600, Top:=50, Height:=400)     'makes a new pie chart

   .Chart.SetSourceData Source:=ActiveSheet.Range(Cells(firstRow, newTextCol), Cells((j - 1), newGbCol))
   .Chart.ChartType = xlPie
   .Chart.HasTitle = True

End With

ActiveSheet.ChartObjects(1).Activate     'selects the newly created chart

With ActiveChart

    .ChartTitle.Text = "Cellphone Data for " & ActiveSheet.Name     'sheet is named by month
    .ClearToMatchStyle     'the first style puts labels on each sections of the pie, which remain even after clearing the style
    .ChartStyle = 261
    .ClearToMatchStyle     'this style puts size percentages under the pie section labels
    .ChartStyle = 259
    .SetElement (msoElementLegendNone)     'removes the legend brought in by one of the styles
End With
End Sub

The last two columns are apps that use at least 2% of the total data. They are what is used to create the pie chart.

app store0.02128921.8clash of clans0.067676
clash of clans0.06767669.3couple0.147461
safari0.300781308system servs0.211914

Last edited:

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.


Well-known Member
Feb 7, 2012
Office Version
  1. 2013
  1. Windows
If your variable j is not greater than one, then you end up with an invalid cell reference in this statement.
.Chart.SetSourceData Source:=ActiveSheet.Range(Cells(firstRow, newTextCol), Cells(j - 1, newGbCol))
VBA does not know how to handle a Cells(0, 6) reference. because it assumes the lowest row to be 1. Same with columns.
When you get the error, click the 'Debug' button and hover the mouse over the j variable and see what the tool tips shows as a value. Apparently, in the first sheet, j was always >=1. No guarantee that is the problem, but it was the only one I saw a the potential cause when I stepped through the code. Your code worked fine with the data in the post.


New Member
Aug 5, 2014
Thank you for your response.

I'm stuck. I threw in some message boxes into my code so that I could see what lines were and werent running as well as the values of some variables.

It turns out that in the sheet that doesn't work j is the proper value (not 0) and all other values that are part of the code you mentioned are the correct values, but that line itself is where I get my error. I even tried just hard-coding in the variable values and it didn't work. :(

However, it still chooses to skip the line that should autofit the 6th (F) column. I've checked the value at that point and it is right. :(
Last edited:

Watch MrExcel Video

Forum statistics

Latest member
thomas Stein

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back