Sharid
Well-known Member
- Joined
- Apr 22, 2007
- Messages
- 1,064
- Office Version
- 2016
- Platform
- Windows
I have a user form, which can remain open on any sheet, when the code below is run it places the data on the active sheet and not on the sheet called "DATA". I have highligted the bit below in red that I this is the issue, could someone please have a look. Many Thanks
Rich (BB code):
Private Sub CommandButton1_Click()
' UserForm1 Textbox input Data
Worksheets("Keywords").Range("C3") = TextBox1.Text
Worksheets("Keywords").Range("C4") = TextBox2.Text
Worksheets("Keywords").Range("C5") = TextBox3.Text
Worksheets("Keywords").Range("C6") = TextBox4.Text
'Keyword URL SCRAPER
Dim IE As Object
Dim HTMLdoc As Object
Dim nextPageElement As Object
Dim div As Object
Dim link As Object
Dim url As String
Dim pageNumber As Long
Dim i As Long
' Takes seach from Keyword Sheet Cell C3 and places it into google
url = "https://www.google.co.uk/search?q=" & Replace(Worksheets("Keywords").Range("C3").Value, " ", "+")
Set IE = CreateObject("InternetExplorer.Application")
With IE
.Visible = True
.navigate url
Do While .Busy Or .readyState <> 4
DoEvents
Loop
End With
Application.Wait Now + TimeSerial(0, 0, 5)
Set HTMLdoc = IE.document
' Searches URLS and places them in Sheet called DATA ROW 2 Column C
pageNumber = 1
i = 2
Do
For Each div In HTMLdoc.getElementsByTagName("div")
If div.getAttribute("class") = "r" Then
Set link = div.getElementsByTagName("a")(0)
Cells(i, 2).Value = link.getAttribute("href")
i = i + 1
End If
Next div
' Searches Number of Pages entered in Keyword Sheet Cell C4
If pageNumber >= Replace(Worksheets("Keywords").Range("C4").Value, " ", "+") Then Exit Do
On Error Resume Next
Set nextPageElement = HTMLdoc.getElementById("pnnext")
If nextPageElement Is Nothing Then Exit Do
' Scrolls Down the Browser
IE.document.parentWindow.Scroll 0&, 99999
'Random delay from Max number entered in Keyword sheet C5
Application.Wait Now + TimeSerial(0, 0, Application.RandBetween(1, Worksheets("Keywords").Range("C5").Value))
nextPageElement.Click 'next web page
Do While IE.Busy Or IE.readyState <> 4
DoEvents
Loop
'Random delay from Max number entered in Keyword sheet C6
Application.Wait Now + TimeSerial(0, 0, Application.RandBetween(1, Worksheets("Keywords").Range("C6").Value))
Set HTMLdoc = IE.document
pageNumber = pageNumber + 1
Loop
IE.Quit
Set IE = Nothing
Set HTMLdoc = Nothing
Set nextPageElement = Nothing
Set div = Nothing
Set link = Nothing
MsgBox "All Done"
End Sub