Application-defined or object-defined error...

[marcucciboy2]

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

	gb	mb	kb			gb
app store	0.021289	21.8			clash of clans	0.067676
clash of clans	0.067676	69.3			couple	0.147461
couple	0.147461	151			mail	0.059082
mail	0.059082	60.5			messenger	0.044629
messenger	0.044629	45.7			pandora	0.049512
pandora	0.049512	50.7			safari	0.300781
photos	0.000219		230		snapchat	0.113281
safari	0.300781	308			system servs	0.211914
snapchat	0.113281	116

<tbody>
</tbody>

 

[JLGWhiz]

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.

 

[marcucciboy2]

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.