VBA - How to loop through each row for x number of rows

xenaga

New Member
Joined
Dec 4, 2020
Messages
5
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi Guys,
I have been trying to figure this out and looking everywhere on the web but I am stuck. I have no experience with VBA except for 2 hours on the web and figured out how to post data onto a website that I am doing.
My data is in A - E and 10 or 50 rows, it doesn't matter. I cant figure out how to loop this until the end of the row? It works for the row itself but I need it to repeat for the next 10, 20, or 50 row items.

Can someone point me in the right direction or let me know what the code is to loop? The bold is highlighted, so it does everything in E2 and fills it out for A2-C2. Then it needs to start at E3 and keep repeating.

Sub Fill1()
Dim IE As Object
Dim doc As HTMLDocument
Dim filename As String
Set IE = CreateObject("InternetExplorer.Application")


'Loop here for each value and keep repeating until the last row.

IE.Visible = True
IE.navigate Sheets("Export").Range("E2").Value
Do While IE.Busy
Application.Wait DateAdd("s", 1, Now)
Loop
Set doc = IE.document
doc.getElementById("tbLocalTitle").Value = ThisWorkbook.Sheets("Export").Range("A2").Value
doc.getElementById("txtKeywords").Value = ThisWorkbook.Sheets("Export").Range("B2").Value
doc.getElementById("txtLoDescription").Value = ThisWorkbook.Sheets("Export").Range("C2").Value
doc.getElementById("LanguageControl_LangCB_Arrow").Click
doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
doc.getElementById("SubmitButton").Click
Application.Wait DateAdd("s", 2, Now)
IE.Quit

'stop loop and start again

End Sub


Thanks in advance
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
VBA Code:
Sub Fill1()
   Dim IE As Object
   Dim doc As HTMLDocument
   Dim filename As String
   Dim i As Long
   Set IE = CreateObject("InternetExplorer.Application")
   IE.Visible = True
   IE.navigate Sheets("Export").Range("[B]E2[/B]")
   Do While IE.Busy
       Application.Wait DateAdd("s", 1, Now)
   Loop
   Set doc = IE.document
  
   With ThisWorkbook.Sheets("Export")  'this just saves a lot of typing below
       i = 2  'start at row 2
       Do While .Cells(i,1) <>""  'keep looping until col A is blank
          doc.getElementById("tbLocalTitle").Value = .Cells(i,1) 'fill with A
          doc.getElementById("txtKeywords").Value = .Cells(i,2) 'fill with B
          doc.getElementById("txtLoDescription").Value = .Cells(i,3) 'fill with C
          doc.getElementById("LanguageControl_LangCB_Arrow").Click
          doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
          doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
          doc.getElementById("SubmitButton").Click
          Application.Wait DateAdd("s", 2, Now)
          i=i+1  'go to next row
      Loop
  End With
  IE.Quit

End Sub
 
Upvote 0

VBA Code:
Sub Fill1()
   Dim IE As Object
   Dim doc As HTMLDocument
   Dim filename As String
   Dim i As Long
   Set IE = CreateObject("InternetExplorer.Application")
   IE.Visible = True
   IE.navigate Sheets("Export").Range("[B]E2[/B]")
   Do While IE.Busy
       Application.Wait DateAdd("s", 1, Now)
   Loop
   Set doc = IE.document
 
   With ThisWorkbook.Sheets("Export")  'this just saves a lot of typing below
       i = 2  'start at row 2
       Do While .Cells(i,1) <>""  'keep looping until col A is blank
          doc.getElementById("tbLocalTitle").Value = .Cells(i,1) 'fill with A
          doc.getElementById("txtKeywords").Value = .Cells(i,2) 'fill with B
          doc.getElementById("txtLoDescription").Value = .Cells(i,3) 'fill with C
          doc.getElementById("LanguageControl_LangCB_Arrow").Click
          doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
          doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
          doc.getElementById("SubmitButton").Click
          Application.Wait DateAdd("s", 2, Now)
          i=i+1  'go to next row
      Loop
  End With
  IE.Quit

End Sub

You are the man, thank you! It helps to me see I need to make it like rows and not like columns which is what I typically do in Excel.
I get an error on this line: IE.navigate Sheets("Export").Range("E2")

Shouldn't that line be in the "Do While" statement? The URL to go to the website is in each of the cells there so it would navigate to the URL in E2, do all the actions from A2-C2, and then go onto E3 etc. I tried putting it in the Do While Cells and write it like you have but that didn't see to work either. But I will try to google this now that I know which statement to use and hopefully find the answer online or if you have a minute to correct, that would be helpful.

Thanks again this was a big help.
 
Upvote 0
try this

VBA Code:
       Sub Fill1()
Dim IE As Object
Dim doc As HTMLDocument
Dim filename As String
Dim i As Long
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

With ThisWorkbook.Sheets("Export") 'this just saves a lot of typing below
i = 2 'start at row 2
Do While .Cells(i,1) <>""  'keep looping until col A is blank
  IE.navigate Sheets("Export").Range("[B]" & cells(i,5) & "[/B]")
  Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
  Loop
  Set doc = IE.document
doc.getElementById("tbLocalTitle").Value = .Cells(i,1) 'fill with A
doc.getElementById("txtKeywords").Value = .Cells(i,2) 'fill with B
doc.getElementById("txtLoDescription").Value = .Cells(i,3) 'fill with C
doc.getElementById("LanguageControl_LangCB_Arrow").Click
doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
doc.getElementById("SubmitButton").Click
Application.Wait DateAdd("s", 2, Now)
i=i+1 'go to next row
Loop
End With
IE.Quit

End Sub
 
Upvote 0
try this

VBA Code:
       Sub Fill1()
Dim IE As Object
Dim doc As HTMLDocument
Dim filename As String
Dim i As Long
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

With ThisWorkbook.Sheets("Export") 'this just saves a lot of typing below
i = 2 'start at row 2
Do While .Cells(i,1) <>""  'keep looping until col A is blank
  IE.navigate Sheets("Export").Range("[B]" & cells(i,5) & "[/B]")
  Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
  Loop
  Set doc = IE.document
doc.getElementById("tbLocalTitle").Value = .Cells(i,1) 'fill with A
doc.getElementById("txtKeywords").Value = .Cells(i,2) 'fill with B
doc.getElementById("txtLoDescription").Value = .Cells(i,3) 'fill with C
doc.getElementById("LanguageControl_LangCB_Arrow").Click
doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
doc.getElementById("SubmitButton").Click
Application.Wait DateAdd("s", 2, Now)
i=i+1 'go to next row
Loop
End With
IE.Quit

End Sub
It just opens up a blank internet explorer with an error on this line: IE.navigate Sheets("Export").Range("" & cells(i,5) & "")
Error says Applicaiton-defined or object-defined error.
 
Upvote 0
It just opens up a blank internet explorer with an error on this line: IE.navigate Sheets("Export").Range("" & cells(i,5) & "")
Error says Applicaiton-defined or object-defined error.
So i googled this error, the range does exists and I am trying it with 3 links in there but keep getting this error.
 
Upvote 0
So i googled this error, the range does exists and I am trying it with 3 links in there but keep getting this error.
I got this to work by changing that line of code to: IE.navigate Sheets("Export").Cells(i, 5).Value

Below is the final code. Thanks guy for your help!

VBA Code:
    Sub Fill1()
Dim IE As Object
Dim doc As HTMLDocument
Dim filename As String
Dim i As Long
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

With ThisWorkbook.Sheets("Export") 'this just saves a lot of typing below
i = 2 'start at row 2
Do While .Cells(i, 1) <> "" 'keep looping until col A is blank
  IE.navigate Sheets("Export").Cells(i, 5).Value
  Do While IE.Busy
    Application.Wait DateAdd("s", 1, Now)
  Loop
  Set doc = IE.document
doc.getElementById("tbLocalTitle").Value = .Cells(i, 1) 'fill with A
doc.getElementById("txtKeywords").Value = .Cells(i, 2) 'fill with B
doc.getElementById("txtLoDescription").Value = .Cells(i, 3) 'fill with C
doc.getElementById("LanguageControl_LangCB_Arrow").Click
doc.getElementById("LanguageControl_LangCB_i3_Label1").Click
doc.getElementById("LanguageControl_LangCB_i8_Label1").Click
doc.getElementById("SubmitButton").Click
Application.Wait DateAdd("s", 2, Now)
i = i + 1 'go to next row
Loop
End With
IE.Quit

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top