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.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]gb[/TD]
[TD]mb[/TD]
[TD]kb[/TD]
[TD][/TD]
[TD][/TD]
[TD]gb[/TD]
[/TR]
[TR]
[TD]app store[/TD]
[TD]0.021289[/TD]
[TD]21.8[/TD]
[TD][/TD]
[TD][/TD]
[TD]clash of clans[/TD]
[TD]0.067676[/TD]
[/TR]
[TR]
[TD]clash of clans[/TD]
[TD]0.067676[/TD]
[TD]69.3[/TD]
[TD][/TD]
[TD][/TD]
[TD]couple[/TD]
[TD]0.147461[/TD]
[/TR]
[TR]
[TD]couple[/TD]
[TD]0.147461[/TD]
[TD]151[/TD]
[TD][/TD]
[TD][/TD]
[TD]mail[/TD]
[TD]0.059082[/TD]
[/TR]
[TR]
[TD]mail[/TD]
[TD]0.059082[/TD]
[TD]60.5[/TD]
[TD][/TD]
[TD][/TD]
[TD]messenger[/TD]
[TD]0.044629[/TD]
[/TR]
[TR]
[TD]messenger[/TD]
[TD]0.044629[/TD]
[TD]45.7[/TD]
[TD][/TD]
[TD][/TD]
[TD]pandora[/TD]
[TD]0.049512[/TD]
[/TR]
[TR]
[TD]pandora[/TD]
[TD]0.049512[/TD]
[TD]50.7[/TD]
[TD][/TD]
[TD][/TD]
[TD]safari[/TD]
[TD]0.300781[/TD]
[/TR]
[TR]
[TD]photos[/TD]
[TD]0.000219[/TD]
[TD][/TD]
[TD]230[/TD]
[TD][/TD]
[TD]snapchat[/TD]
[TD]0.113281[/TD]
[/TR]
[TR]
[TD]safari[/TD]
[TD]0.300781[/TD]
[TD]308[/TD]
[TD][/TD]
[TD][/TD]
[TD]system servs[/TD]
[TD]0.211914[/TD]
[/TR]
[TR]
[TD]snapchat[/TD]
[TD]0.113281[/TD]
[TD]116[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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