Home How to repeat VBA macro with cell references changing
Reply: 0

How to repeat VBA macro with cell references changing

user6231 Published in September 19, 2018, 9:48 am

I have written the following code which scrapes webpages based on a URL (in cell B2) and then outputs a number scraped from the page in C2.

Sub Scrape()
    Dim ObjIE As Object
    Set ObjIE = CreateObject("InternetExplorer.Application")
    ObjIE.Navigate Sheets("facebook").Range("B2").Value

    Application.StatusBar = "Loading, Please wait..."

    Do While ObjIE.Busy
        Application.Wait DateAdd("s", 1, Now)

    Application.StatusBar = "Searching for value. Please wait..."

    Dim dd As String
    dd = ObjIE.Document.getElementsByClassName("_3xom")(0).innerText

    Sheets("Facebook").Range("C2") = dd

    Dim cell As Range
End Sub

I have more URLs in column B. I need to find some code which will repeat the macro but change the two cell references to "B3" and "C3" respectively, and then 4, 5, 6 and so forth. I've tried all kind of loops but cant work out what might make this work!

Any help would be super appreciated.

share|improve this question
  • 1
    Look at using .Cells instead of .Range. With that type of cell reference you can set up a loop such as Dim i As Long; For i = 1 To 10; ObjIE.Navigate Sheets("facebook").Cells(i,2).Value – PeterT Apr 16 at 13:59

2 Answers 2

active oldest votes
up vote 2 down vote accepted
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.324152 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO