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!
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]
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: