Coding 2 loops in VBA

DMaynard

New Member
Joined
Dec 2, 2016
Messages
4
Hi,

I don't have a lot of experience with macros but I managed to build the start of something that works using the macro recorder (See below). The issue that I'm running into now is that I want to code a loop into the macro. I have a list of websites in column G and I want to open them in order (i.e. I'm selecting G2 in the first step here, but after it runs once I want it to loop back and do G3, and so on). Similarly I paste my values into sheet 2 cell B2, and on the next loop I'd like it to end up in B3, B4, etc.. Ideally I'd like it to loop 1000 times.

Thanks in advance for any help you can offer!

Code:
Sub Macro10()
'
' Macro10 Macro
'


'
    Range("G2").Select
    Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    Range("P3").Select
    Range("A2:F2").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B3").Select
    Sheets("Sheet1").Select
    Range("A1").Select
End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
635
Office Version
  1. 365
Platform
  1. Windows
I'm afraid I did not quite understand about the loop that you want to replicate data from "A2:F2" to "Sheet2", anyway here goes a code as a starting point.
Code:
Sub Macro10V2()
 Dim Hyperweb As Range

' Macro10V2 Macro
'
  For Each Hyperweb In Range("G2:G" & Cells(Rows.Count, 7).End(3).Row)
    Hyperweb.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    
    Sheets("Sheet2").Cells(Rows.Count, 2).End(3)(2).Resize(, 6).Value = Range("A2:F2").Value
    
  Next Hyperweb
End Sub
 

DMaynard

New Member
Joined
Dec 2, 2016
Messages
4
I'm afraid I did not quite understand about the loop that you want to replicate data from "A2:F2" to "Sheet2"

Thanks, basically I have a long list of webpages in column G. I want to access the first webpage and paste the contents of the page into column P. I then have formulas in A2:F2 that extract data from the webpage text. I want to copy the data that I extract with those formulas from A2:F2 on Sheet 1 to B2:G2 on Sheet 2.

I tried running the code you sent and the looping part worked great. But I ran into 2 problems. (1) The code opens up 1000's of web pages-- Ideally I'd like to close the page after copying the content of the page, and (2) the code seems to only copy the information from the second webpage on the list to the table on sheet 2 (not sure why this is happening, I ran into the same issue when I tried to use the macro recorder myself).

I'd really appreciate any advice you can give. Thanks so much for your help!
 

DMaynard

New Member
Joined
Dec 2, 2016
Messages
4
Thanks, basically I have a long list of webpages in column G. I want to access the first webpage and paste the contents of the page into column P. I then have formulas in A2:F2 that extract data from the webpage text. I want to copy the data that I extract with those formulas from A2:F2 on Sheet 1 to B2:G2 on Sheet 2.


Sorry, should add that after it does one iteration of that I'd like it to loop back and do the next webpage and copy the info into B3:G3 on sheet 2, etc...
 

Watch MrExcel Video

Forum statistics

Threads
1,129,592
Messages
5,637,291
Members
416,963
Latest member
zazama

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
Top