VBA: Subscript out of range or Type Mismatch

Question

Very new to VBA, and am really stuck. Below is my code, you'll see near the end my For loop for Des and DesArr. All I'm trying to do with that loop is pull a column of cells from the work sheet "SIC", which is Sheet2 in my Workbook, I either get the error "Subscript out of Range" or "Type Mismatch" and whenever I try and google/correct for one, the other error takes its place. If anyone can help me work through this I'd greatly appreciate it!

Public Sub getGoogleDescriptions(strSearch As String)
    Dim URL As String, strResponse As String
    Dim objHTTP As Object
    Dim htmlDoc As HTMLDocument
    Dim result As String
    Dim i As Integer
    Dim u As Integer
    Dim resultArr As Variant
    Dim Des As String
    Dim DesArr(2 To 48) As Long


    Set htmlDoc = CreateObject("htmlfile")
    'Set htmlDoc = New HTMLDocument

    Dim objResults As Object
    Dim objResult As Object

    strSearch = Replace(strSearch, " ", "+")

    URL = "https://www.google.com/search?q=" & strSearch

    Set objHTTP = CreateObject("MSXML2.XMLHTTP")

    With objHTTP
        .Open "GET", URL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        htmlDoc.body.innerHTML = .responseText
    End With

    Set objResults = htmlDoc.getElementsByClassName("st")

    Debug.Print objResults(0).innerText

    result = CStr(objResults(0).innerText)
    resultArr = Split(result, " ", -1, 0)
    For i = LBound(resultArr) To UBound(resultArr) 'Define i to be the length of the List'
        Debug.Print i, resultArr(i) 'Prints the corresponding index value and array element'
    Next i 'repeat

    Set htmlDoc = Nothing
    Set objResults = Nothing
    Set objHTTP = Nothing


    Set wk = ActiveWorkbook

    For u = 2 To 48
        Des = Sheets("SIC").Range("C" & u).Value
        DesArr(u) = Des
    Next u

    Debug.Print DesArr(2)

End Sub

Answer 1

You're getting type mismatch because it's expecting DesArr to be a long data type which is a number between -2,147,483,648 to 2,147,483,647.

1. In it's use within the subroutine, it's used as a variant. So 2 corrections - change it to a variant as shown below

2. Then just adjust your 2 to 48 to within your statement... in this case it's a simple offset of 2, so just use (u - 2) and your Variant length is 47 starting at 0 instead of 1.

   Public Sub getGoogleDescriptions(strSearch As String)
   
       Dim URL As String, strResponse As String
       Dim objHTTP As Object
       Dim htmlDoc As HTMLDocument
       Dim result As String
       Dim i As Integer
       Dim u As Integer
       Dim resultArr As Variant
       Dim Des As String
       Dim DesArr(0) : ReDim DesArr(46)
   
       Set htmlDoc = CreateObject("htmlfile")
       'Set htmlDoc = New HTMLDocument
   
       Dim objResults As Object
       Dim objResult As Object
   
       strSearch = Replace(strSearch, " ", "+")
   
       URL = "https://www.google.com/search?q=" & strSearch
   
       Set objHTTP = CreateObject("MSXML2.XMLHTTP")
   
       With objHTTP
           .Open "GET", URL, False
           .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
           .send
           htmlDoc.body.innerHTML = .responseText
       End With
   
       Set objResults = htmlDoc.getElementsByClassName("st")
   
       Debug.Print objResults(0).innerText
   
       result = CStr(objResults(0).innerText)
       resultArr = Split(result, " ", -1, 0)
       For i = LBound(resultArr) To UBound(resultArr) 'Define i to be the length of the List'
           Debug.Print i, resultArr(i) 'Prints the corresponding index value and array element'
       Next i 'repeat
   
       Set htmlDoc = Nothing
       Set objResults = Nothing
       Set objHTTP = Nothing
   
   
       Set wk = ActiveWorkbook
   
       For u = 2 To 48
           Des = Sheets("SIC").Range("C" & u).Value
           DesArr(u - 2) = Des
       Next u
   
       Debug.Print DesArr(0)
   

   End Sub