Application-defined or object-defined error...

marcucciboy2

New Member
Joined
Aug 5, 2014
Messages
23
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!

Code:
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
Loop

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

Columns(newTextCol).AutoFit


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.

gbmbkbgb
app store0.02128921.8clash of clans0.067676
clash of clans0.06767669.3couple0.147461
couple0.147461151mail0.059082
mail0.05908260.5messenger0.044629
messenger0.04462945.7pandora0.049512
pandora0.04951250.7safari0.300781
photos0.000219230snapchat0.113281
safari0.300781308system servs0.211914
snapchat0.113281116

<tbody>
</tbody>
 
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.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
If your variable j is not greater than one, then you end up with an invalid cell reference in this statement.
Code:
.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.
 

marcucciboy2

New Member
Joined
Aug 5, 2014
Messages
23
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

Threads
1,122,437
Messages
5,596,130
Members
414,043
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 MrExcel.com.
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 "mrexcel.com".
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
Top