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

X

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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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
Dermot said:
2
Click to expand...

Dermot said:
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
Click to expand...

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
Dermot said:
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
Click to expand...
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
xenaga said:
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.
Click to expand...
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
xenaga said:
So i googled this error, the range does exists and I am trying it with 3 links in there but keep getting this error.
Click to expand...
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
You must log in or register to reply here.

Similar threads

Y
  • Question Question
How to select Gender from Dropdown list using Excel VBA?
Replies
0
Views
676
YJones
Y
A
  • Solved
Using VBA to loop through multiple Word Templates and fill them in with Excel Data
2
Replies
14
Views
2K
John_w
J
B
  • Question Question
Extract Search Output from Website
Replies
2
Views
908
BigRusty82
B
B
  • Question Question
How to increment cell (A2 to A3..A4..) and so on using selenium macros excel.
Replies
1
Views
859
BasitAmeer
B
B
  • Question Question
How do I export charts based on conditions to PDF using VBA?
Replies
1
Views
1K
John_w
J

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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