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

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
715
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...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,489
Messages
5,837,640
Members
430,506
Latest member
TonyIbbs

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