Sub СЕМЕЧКИ()
    Dim url$, txt$
    Dim i&
'------------------
    With ActiveSheet
        a = .UsedRange.Value
        For i = 2 To UBound(a)
            If a(i, 1) <> "" And a(i, 2) <> "" Then
                url = "https://www.mikado-parts.ru/ws1/service.asmx/CodeBrandStockInfo?Code=" & a(i, 1) & "&Brand=" & a(i, 2) & "&ClientID=10200&Password=turg0404 HTTP/1.1"
                txt = GetHttp(url)
                .Cells(i, 3) = GetXml(txt, "CodeBrandResult/List/CodeBrandLine/PriceRUR")
            End If
        Next
    End With
    Beep
    MsgBox "Готово!"
End Sub

Function GetXml(ByVal XmlText As String, ByVal NodePath As String) As String
    With CreateObject("MSXML2.DOMDocument")
        .async = False
        .validateOnParse = False
        .LoadXML (XmlText)
        GetXml = .SelectSingleNode(NodePath).TEXT
    End With
End Function

Function GetHttp(ByVal url As String) As String
    With CreateObject("Microsoft.XMLHTTP")
        .Open "GET", url, "False"
        .setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"
        .setRequestHeader "Accept-Encoding", "gzip, deflate, sdch"
        .setRequestHeader "user-agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/44.0.2403.155 Safari/537.36"
        .setRequestHeader "Accept-Language", "ru,en-US;q=0.8,en;q=0.6"
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        If .Status = 200 Then
            GetHttp = .responsetext
        End If
    End With
End Function

Sub СЕМЕЧКИ()
    Dim url$, txt$
    Dim i&
'------------------
    With ActiveSheet
        a = .UsedRange.Value
        For i = 2 To UBound(a)
            If a(i, 1) <> "" And a(i, 2) <> "" Then
                url = "https://www.mikado-parts.ru/ws1/service.asmx/CodeBrandStockInfo?Code=" & a(i, 1) & "&Brand=" & a(i, 2) & "&ClientID=10200&Password=turg0404 HTTP/1.1"
                txt = GetHttp(url)
                .Cells(i, 3) = GetXml(txt, "CodeBrandResult/List/CodeBrandLine/PriceRUR")
            End If
        Next
    End With
    Beep
    MsgBox "Готово!"
End Sub

Function GetXml(ByVal XmlText As String, ByVal NodePath As String) As String
    With CreateObject("MSXML2.DOMDocument")
        .async = False
        .validateOnParse = False
        .LoadXML (XmlText)
        GetXml = .SelectSingleNode(NodePath).TEXT
    End With
End Function

Function GetHttp(ByVal url As String) As String
    With CreateObject("Microsoft.XMLHTTP")
        .Open "GET", url, "False"
        .setRequestHeader "Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8"
        .setRequestHeader "Accept-Encoding", "gzip, deflate, sdch"
        .setRequestHeader "user-agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/44.0.2403.155 Safari/537.36"
        .setRequestHeader "Accept-Language", "ru,en-US;q=0.8,en;q=0.6"
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send
        If .Status = 200 Then
            GetHttp = .responsetext
        End If
    End With
End Function

Function GetXml(ByVal XmlText As String, ByVal NodePath As String) As String
    
    Debug.Print XmlText ' Посмотрите, что приходит в ответ
    With CreateObject("MSXML2.DOMDocument")
        .async = False
        .validateOnParse = False
        .LoadXML (XmlText)
        Set nod = .SelectSingleNode(NodePath)
        If Not nod Is Nothing Then
        GetXml = .Text
        End If
    End With
End Function