VBA prints the contents of the array in the specified cell and then jumps out of the loop-CodePudding

Thanks for watching, I have a list with urls in D and E, I need to get the value in XML and print it after F.then wrap to the next line.

Sub main()
    On Error Resume Next
    hang1 = Sheet1.Cells(Rows.Count, "a").End(xlUp).Row
    If hang1 < 2 Then Exit Sub

    sz_yuan = Sheet1.Range("D2:E" & hang1)

    Set xmlDoc = CreateObject("Microsoft.XMLDOM")
    xmlDoc.SetProperty "SelectionLanguage", "XPath"
    xmlDoc.async = False


    With CreateObject("MSXML2.XMLHTTP")

        Set nodeXML = xmlDoc.getElementsByTagName("Business_Item_Desc")

        For a = 1 To hang1 - 1
            For b = 1 To 2
                Application.StatusBar = "capture" & a & "/" & hang1 - 1 & "Article of data" & b & "/2..."
                DoEvents
                URL0 = sz_yuan(a, b)
                .Open "GET", URL0, True
                .send
                Do While .ReadyState <> 4
                    DoEvents
                Loop
                jg0 = .responseText
        
                If InStr(jg0, "<data/>") = 0 Then
                    For i = 0 To nodeXML.Length - 1
                        Sheet1.Range("F").Offset(0, i).Value = nodeXML.Item(i).Text
                    Next i
                    Exit For
                End If
                dqhang = Sheet1.Cells(Rows.Count, "a").End(xlUp).Row
            Next b
        Next a
    End With

    Application.StatusBar = ""
End Sub

Among them, there is an error when the array is printed, and I want to ask how to modify it to run normally.

For i = 0 To nodeXML.Length - 1
    Sheet1.Range("F").Offset(0, i).Value = nodeXML.Item(i).Text
Next i

CodePudding user response：

The problem is that when you say

Sheet1.Range("F")

VBA does not know what you mean. Change it to

Sheet1.Range("F1")

And I think it will get rid of the error.

Looking at your code, you may perfer this as I think it will get you closer to the final goal

sheet1.cells(a,"F")

CodePudding user response：

If I understand correctly, you want to parse the XML at the URL in cell D2 and put the found items beginning if F2. So if the XML from D2 finds three values, they will go in F2, G2, and H2. Then you parse the XML from E2 and continue to add the values at the end of row 2, so if the XML from E2 gives 2 values, they would go in I2 and J2. IF so, This line will do it.

Sheet1.Cells(sz_yuan(a, b).row, Sheet1.Columns.Count).End(xlToLeft).Offset(0, 1).Value = nodeXML.Item(i).Text