VBA：下标超出范围或类型不匹配

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!

对VBA来说很新,我真的被卡住了。下面是我的代码,你会看到我的For循环为Des和DesArr。我正在尝试使用该循环从工作表“SIC”拉出一列单元格,这是我的工作簿中的Sheet2,我得到错误“下标超出范围”或“类型不匹配”,每当我尝试和谷歌/纠正一个,另一个错误取代它的位置。如果有人能帮助我完成这项工作,我将非常感激!

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

1 个解决方案

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

你得到类型不匹配,因为它期望DesArr是一个长数据类型,它是-2,147,483,648到2,147,483,647之间的数字。

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.

然后在你的陈述中调整你的2到48 ......在这种情况下,它是2的简单偏移,所以只需使用(u - 2),你的变体长度是47从0开始而不是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

#1