Object Required error on two as-near-as-I-can-tell valid objects

Question

Can anyone help me figure out why I'm getting an object required error on this code? The line in question is

comnum(a) = comnum(a)+1

I used msgbox to make sure both of these things actually worked, and it spat them out just fine. But for reasons unknown to me, while vba is quite happy to read out both of those individually, setting them equal is a problem?

I cut out a bit of code that doesn't interact with this line in order to save on space, I hope no one minds:

Sub create_sheets(mms, dds, yys, mme, dde, yye)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim fso As New FileSystemObject
Dim wb1 As Workbook
Dim wb2 As Workbook

Set wb1 = ThisWorkbook

dd = dds
mm = mms
yyyy = yys

'this is where all that unrelated crap went
'it's just formatting, checking if a filepath exists, and if it does_
'opening the file and copying the data.  
'It's like 60 unrelated lines, hence my omission

Dim comcol As New Collection
Dim comnum As New Collection

            While wb1.Sheets(1).Cells(1, 4) <> ""                               'as long as data remains
            strnum = 0                                                          'start with no counts of the string
            stro = wb1.Sheets(1).Cells(1, 4)                                    'the string is the contents of the first row's string column
            comcol.Add (wb1.Sheets(1).Cells(1, 5))
            comnum.Add (0)

                For c = 1 To 65536                                              'for all rows
                    If wb1.Sheets(1).Cells(c, 4) = stro Then                    'if you see the string
                        strnum = strnum + 1                                     'count it
                        For a = 1 To comcol.Count                               'go through the entire collection
                            If comcol(a) = wb1.Sheets(1).Cells(c, 5) Then       'if it finds the string, increments the count of it
                                MsgBox comnum(a) & " and " & comnum(a) + 1
                                comnum(a) = comnum(a) + 1
                            End If
                        Next a
                        wb1.Sheets(1).Row(c).Delete
                        c = c - 1
                    End If
                Next c

                Sheets("Results").Activate

                Cells(3, 2) = Sheets(1).Cells(1, 4)
                While comcol.Count <> 0
                    Cells(4, 2) = comcol(1) & ", appeared " & comcol(2) & "times"
                Wend
            Wend

Thanks in advance!

Please see [this post](http://stackoverflow.com/q/10714251/1188513) for why and how you should avoid `Select` and `Activate`. — Mathieu Guindon, Dec 07 '16 at 20:02
I replaced msgbox with debug.print, but I don't see what it's doing (sorry, I'm still pretty new to vba). How do I see the results of it? Also, I thought I got all the selects and activates, I saw a few days ago that they were considered bad, seems I missed one, thanks! — kumquatwhat, Dec 07 '16 at 20:04
Ctrl+G would bring up the immediate pane. But I removed that comment - the real problem is in the answer below: you're trying to update a collection, and you can't do that. — Mathieu Guindon, Dec 07 '16 at 20:05
Don't forget to mark the answer as "accepted" by clicking the hollow checkmark next to it; that will remove your question from the pool of "unanswered" posts. — Mathieu Guindon, Dec 07 '16 at 20:07

Storax · Accepted Answer · 2016-12-07T19:52:26.893

1

You cannot update the value stored at the Key. Would you like to do sth like that

comnum.Add comnum(a) + 1

see my comment

edited Dec 07 '16 at 19:52

answered Dec 07 '16 at 19:41

Storax

11,158
3
16
33

Sorry, I got it wrong. comnum is a collection, i.e. you cannot update the value stored at the Key. – Storax Dec 07 '16 at 19:50
Is there a way to replace it so that it's stored at the same place? comcol and comnum (are supposed to) work so that comnum(a) tracks how many times comcol(a) appears; if they get out of order, it doesn't really work. Or is that not possible and I should just create a dummy variable to count and then at the very end just add comcol.add (dummy_variable)? – kumquatwhat Dec 07 '16 at 20:07
@GrahamPerry you might want to look at `Scripting.Dictionary` instead of a `Collection` (reference the *Microsoft Scripting Runtime* library from the "Tools" menu) – Mathieu Guindon Dec 07 '16 at 20:08
I will look into it. Thanks again. – kumquatwhat Dec 07 '16 at 20:11

Object Required error on two as-near-as-I-can-tell valid objects

1 Answers1