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
 

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
550
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...
 

Forum statistics

Threads
1,081,991
Messages
5,362,593
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top