VBA Loop through row and return value

Question

Really would appreciate some help on this. I have a vba script that scrapes data, it opens the URL that is contained in the L column, L4 in this example. Then later down the script it inputs my given value into Col E, Row 4.

Sub ImportData()
...
With CreateObject("msxml2.xmlhttp")
.Open "GET", Range("L4"), False 'Cell that contains hyperlink
.send
HTML_Content.body.innerHTML = .responseText
End With
...

'Then I want to return a value
Sheets(1).Range("E4").Value = rng1.Offset(0, 1)
End Sub

I am trying to make a loop so that the script runs automatically and cycles through column L and runs the script for every row that contains a hyperlink in Col L, and then inputs the value to its respective row in Col E.

I have tried changing the code below that another user suggested without success:

Sub ImportData(urlToOpen as string)
...
.Open "GET", urlToOpen, False 'Cell that contains hyperlink
...
'Then I want to return a value
Sheets(1).Range(E, i).Value = rng1.Offset(0, 1) ' I know that's wrong

and add a calling procedure:

Sub CallRangeL_Urls()
For Each i In Sheet1.Range("L4:L200")
    Call ImportData(i)
Next i
End Sub

I keep getting ByRef type argument mismatch error on Call ImportData(i)

Also I am not sure what so ever on how to acheive calling the value to the specific row that is being processed in the loop. Any help would be greatly appreciated. Thanks

QHarr · Accepted Answer · 2019-02-24 09:54:08Z

1

Try the following:

Public Sub ImportData(ByVal urlToOpen As String)

And

Public Sub CallRangeL_Urls()
    Dim i As Range
    For Each i In Sheet1.Range("L4:L200")
        ImportData i.Value
    Next i
End Sub

Personally, I would reference the workbook as well and I usually use Worksheets("SheetName") but I know a lot of people like to use codeName.

You only want to pass the value of the link in the cell so ByVal is the appropriate way.

As touching the sheet is expensive I would probably dump the urls in an array and loop that, adding a basic test that I am working with an url:

Public Sub CallRangeL_Urls()
    Dim arr(), i As Long
    arr = Application.Transpose(Sheet1.Range("L4:L200").Value)
    For i = LBound(arr) To UBound(arr)
        If InStr(arr(i), "http") > 0 Then ImportData arr(i)
    Next i
End Sub

To write out extracted value to column E, same row as url, I think you need to convert your ImportData sub to a function that returns the extracted value. Or better still, create a class to hold the xmlhttp object which then has a method to return the value (that way you don't keep creating and destroying the object - which you do if you create the object in the function. You could also create the xmlhttp object in the first sub and pass to the function as an argument - I show you pseudo code for that below).

Public Sub CallRangeL_Urls()
    Dim arr(), i As Long
    'code to create xmlhttp object
    arr = Application.Transpose(Sheet1.Range("L4:L200").Value)
    For i = LBound(arr) To UBound(arr)
        If InStr(arr(i), "http") > 0 Then
            Sheet1.Cells(i + 3, "E") = ImportData(arr(i), xmlhttpObject)
        End If
    Next i
End Sub

Public Function ImportData(ByVal urlToOpen As String, ByVal xmlhttpObject As Object) As String
    ''Any declarations
    'Dim extractedValue As String
    'Dim html As HTMLDocument
    'Set html = New HTMLDocument
    With xmlhttpObject
        .Open "GET", urlToOpen, False
        .send
        html.body.innerHTML = .responseText
        ''code to extract value
        'extractedValue = html.querySelector("someSelector")
    ImportData = extractedValue
End Function

edited Feb 24, 2019 at 9:54

answered Feb 24, 2019 at 9:10

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

James Over a year ago

Amazing! Thank you so much, it works perfectly! Thank you for taking the time to help me. How could I now input the data to a cell in a specific column on that row? So if the script has just opened the URL on L4, my existing code that enters the data is Sheets(1).Range("E4").Value = rng1.Offset(0, 1), how can I tell it to input the data from rng1.Offset(0, 1) into Column E and it's current row? Is there any way of using i for that?

QHarr Over a year ago

so, same sheet you want the result in the same row as the url but in column E?

James Over a year ago

Exactly that :)

QHarr Over a year ago

I can't see enough code but I suspect that ImportData should be a function that returns a string.

James Over a year ago

Thanks again for the time you have spent helping me, you have saved me a great deal of time and I have learnt something! I will take the pseudo code and get to work with it!! Thank you :)

|

Collectives™ on Stack Overflow

VBA Loop through row and return value

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related