automation error -2146232576 (80131700) on creating an array

Question

I have a macro that's working on one computer just fine, however when I try to run it on another computer I get the following error:

Run-time error '-2146232576 (80131700)' Automation error

Here's the code:

Sub Kaivuri()
Dim i As Long
Dim arvoKohta As Integer
Dim etuKohta As Integer
Dim sukuKohta As Integer
Dim yritysKohta As Integer
Dim tulosMaara As Integer
Dim ws As Worksheet
Dim hakuSana As String
Dim arvo As String
Dim etunimiLista As Object
Dim sukunimiLista As Object
Dim riviLista As Object
Dim kaupunkiLista As Object
Dim lisarivit As Integer

Set kaupunkiLista = CreateObject("System.Collections.ArrayList")
Set riviLista = CreateObject("System.Collections.ArrayList")
Set etunimiLista = CreateObject("System.Collections.ArrayList")
Set sukunimiLista = CreateObject("System.Collections.ArrayList")

Set browserIE = CreateObject("InternetExplorer.Application")
browserIE.Top = 0
browserIE.Left = 800
browserIE.Width = 800
browserIE.Height = 1200
browserIE.Visible = True

Set ws = ThisWorkbook.Worksheets("etsintä")
i = 174958
etuKohta = 1
sukuKohta = 2
yritysKohta = 5
arvoKohta = 20

browserIE.navigate ("https://www.linkedin.com/sales/search?    facet=G&count=25&start=0&updateHistory=true&searchHistoryId=4444924553")
Do While browserIE.ReadyState <> 4 And browserIE.Busy: DoEvents: Loop
Application.Wait (Now + TimeValue("0:00:5"))

browserIE.document.getelementsbyclassname("facet G") (0).getelementsbyclassname("plus-icon-container")(0).getelementsbyclassname("small-icon")(0).Click
browserIE.document.getelementbyid("G-input").Value = "Finland"
'Application.SendKeys ("~ (tilde)")
'Application.Wait (Now + TimeValue("0:00:1"))

r = 0
Do While i <= 175000
If r = 100 Then
    ActiveWorkbook.Save
    r = 0
End If
lisarivit = 0
kaupunkiLista.Add (Cells(i, 8).Value)
riviLista.Add (i)
etunimiLista.Add (Cells(i, etuKohta).Value)
sukunimiLista.Add (Cells(i, sukuKohta).Value)

Debug.Print i
'Debug.Print Cells(i + 1, yritysKohta).Value

If Cells(i, yritysKohta).Value <> Cells(i + 1, yritysKohta).Value Then
    hakuSana = Cells(i, yritysKohta).Value

    browserIE.document.getelementsbyclassname("facet CC")(0).getelementsbyclassname("plus-icon-container")(0).Click
    browserIE.document.getelementbyid("CC-input").Value = hakuSana
    'Application.SendKeys ("~ (tilde)")
    'Application.Wait (Now + TimeValue("0:00:1"))

    tulosMaara = browserIE.document.getelementsbyclassname("spotlight-result-count")(0).innertext

    If tulosMaara <> 0 Then

            p = 1

            Do While p <= tulosMaara
                l = 0

                Do While l <= 24 And p <= tulosMaara
                    nimi = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("name-and-badge-container")(0).getelementsbyclassname("name")(0).innertext
                    Debug.Print nimi
                    Debug.Print "t: " & tulosMaara & " p: " & p
                    If nimi <> "LinkedIn Member" Then
                        etunimi = Left(nimi, InStr(nimi, " ") - 1)
                        sukunimi = Right(nimi, (Len(nimi) - InStr(nimi, " ")))


                        onko = True
                        h = 0
                        pituus = sukunimiLista.Count
                        Do While h < pituus
                            If etunimi = etunimiLista(h) And sukunimi = sukunimiLista(h) Then
                                onko = False
                                Exit Do
                            End If
                            h = h + 1
                        Loop

                        If onko = True Then

                                arvo = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-value")(0).innertext
                                Debug.Print arvo
                                e = 0
                                Do While browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-label")(e).innertext <> "Location: "
                                    e = e + 1
                                Loop

                                Sijainti = browserIE.document.getelementsbyclassname("result loading member")(l).getelementsbyclassname("info")(0).getelementsbyclassname("info-value")(e).innertext
                                Debug.Print Sijainti
                                If InStr(Sijainti, " Area,") <> 0 Then
                                    Sijainti = Left(Sijainti, InStr(Sijainti, " Area,") - 1)
                                End If
                                Debug.Print Sijainti
                                ' rivin lisäys ja värjäys
                                f = riviLista(0)
                                Rows(f).Select

                                Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
                                With Selection.Interior
                                    .Pattern = xlSolid
                                    .PatternColorIndex = xlAutomatic
                                    .Color = 65535
                                    .TintAndShade = 0
                                    .PatternTintAndShade = 0
                                End With

                                i = i + 1
                                lisarivit = lisarivit + 1


                                Cells(f, 8).Value = Sijainti


                                If Sijainti = "Helsinki" Then
                                    h = 0
                                    Do While h < kaupunkiLista.Count
                                        If kaupunkiLista(h) = "Espoo" Or kaupunkiLista(h) = "ESPOO" Or kaupunkiLista(h) = "Helsinki" Or kaupunkiLista(h) = "HELSINKI" Or kaupunkiLista(h) = "VANTAA" Or kaupunkiLista(h) = "Vantaa" Then
                                            Cells(f, 6).Value = Cells(riviLista(h) + lisarivit, 6).Value
                                            Cells(f, 7).Value = Cells(riviLista(h) + lisarivit, 7).Value
                                            Cells(f, 8).Value = Cells(riviLista(h) + lisarivit, 8).Value
                                            Cells(f, 9).Value = Cells(riviLista(h) + lisarivit, 9).Value
                                            Cells(f, 10).Value = Cells(riviLista(h) + lisarivit, 10).Value
                                            Rows(f).Select
                                            With Selection.Interior
                                                .Pattern = xlSolid
                                                .PatternColorIndex = xlAutomatic
                                                .Color = 5287936
                                                .TintAndShade = 0
                                                .PatternTintAndShade = 0
                                            End With
                                            Exit Do
                                        End If
                                        h = h + 1
                                    Loop

                                Else
                                    h = 0
                                    sijainti1 = LCase(Sijainti)
                                    Do While h < kaupunkiLista.Count
                                        vanhasijaintitieto = LCase(kaupunkiLista(h))
                                        If sijaint1 = vanhasijaintitieto Then
                                            Cells(f, 6).Value = Cells(riviLista(h) + 1, 6).Value
                                            Cells(f, 7).Value = Cells(riviLista(h) + 1, 7).Value
                                            Cells(f, 8).Value = Cells(riviLista(h) + 1, 8).Value
                                            Cells(f, 9).Value = Cells(riviLista(h) + 1, 9).Value
                                            Cells(f, 10).Value = Cells(riviLista(h) + 1, 10).Value
                                            Rows(f).Select
                                            With Selection.Interior
                                                .Pattern = xlSolid
                                                .PatternColorIndex = xlAutomatic
                                                .Color = 5287936
                                                .TintAndShade = 0
                                                .PatternTintAndShade = 0
                                            End With
                                            Exit Do
                                        End If
                                        h = h + 1
                                    Loop
                                End If
                                Debug.Print arvo
                                If InStr(arvo, " at ") <> 0 Then
                                    arvo = Left(arvo, InStr(arvo, " at ") - 1)
                                End If

                                Cells(f, etuKohta).Value = etunimi
                                Cells(f, sukuKohta).Value = sukunimi
                                domain = Right(Cells(f + 1, 3).Value, Len(Cells(f + 1, 3).Value) - InStr(Cells(f + 1, 3).Value, "@") + 1)
                                Cells(f, 3).Value = etunimi & "." & sukunimi & domain
                                Cells(f, 4).Value = "notBlocked"
                                Cells(f, 5).Value = Cells(f + 1, 5).Value
                                Cells(f, 11).Value = Cells(f + 1, 11).Value
                                Cells(f, 12).Value = Cells(f + 1, 12).Value
                                Cells(f, 13).Value = Cells(f + 1, 13).Value
                                Cells(f, 14).Value = Cells(f + 1, 14).Value
                                Cells(f, 15).Value = Cells(f + 1, 15).Value
                                Cells(f, 16).Value = Cells(f + 1, 16).Value
                                Cells(f, 17).Value = Cells(f + 1, 17).Value
                                Cells(f, 18).Value = Cells(f + 1, 18).Value
                                Cells(f, 20).Value = arvo
                                arvo = ""
                        End If
                    End If
                    Debug.Print "lopussa: " & p & " : " & tulosMaara
                    l = l + 1
                    p = p + 1
                    If p = tulosMaara + 1 Then Exit Do
                Loop

                If p = tulosMaara + 1 Then Exit Do
                On Error Resume Next
                browserIE.document.getelementsbyclassname("next-pagination page-link")(0).getelementsbyclassname("artdeco-icon")(0).Click
                Do While browserIE.ReadyState <> 4 And browserIE.Busy: DoEvents: Loop
                Application.Wait (Now + TimeValue("0:00:4"))
                On Error GoTo 0

            Loop

    End If


riviLista.Clear
kaupunkiLista.Clear
etunimiLista.Clear
sukunimiLista.Clear
browserIE.document.getelementsbyclassname("facet CC")    (0).getelementsbyclassname("dismiss-selection")(0).Click
End If

i = i + 1

Loop

End Sub

The error appears on line 16:

Set kaupunkiLista = CreateObject("System.Collections.ArrayList")

Any idea why this happens?

Answer 1

I am answering a question that is two and a half years old, but took me way too long to find an answer for this elsewhere, and this still appears near the top of google's results for this issue.

My solution was to install .NET 3.5 even though I had .NET 4.0 installed on my machine. You specifically need the 3.5 version to use System.Collections.ArrayList. Install 3.5 and then close down excel completely and try again.

Here is how to add the library to a PC:

1. Search for "turn windows features on or off". Select the option when it comes up.

2. Select/check the box for .NET Framework 3.5 (you don't necessarily need to expand and check off the child items).

3. Let Windows download and install the required files.

Don't forget to close and reopen Excel (or whatever application you're using).