Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old June 4th, 2008, 02:37 PM
Registered User
Join Date: Jun 2008
Location: San Francisco, CA, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default QueryTables nested loop

I've created a QueryTables nested loop to retrieve stock data using a list of Tickers. The QueryTable loop works fine, but I can't get the Ticker list to loop using "iRow" as the variable. There must be something obvious I'm missing. Help!

Sub WebData()

    Dim Column As Integer
    Dim TheCell As Range
    Dim Issue As Range
    Dim PxData As Range
    Dim aPx As Range
    Dim Symb1 As String
    Dim DatesC, DatesP As Range
    Dim AdjPx As Range
    Dim y, z, T

     Column = 0
     z = 66
     Row = 0
     D = 0
     iRow = 0

     For iRow = 0 To 30

         Symb1 = Sheets("WEBDATA").Range("B2").Offset(iRow, 0)

        Set PxData = Sheets("PxHist").Range("A3:G400")
        Set aPx = Sheets("PxAdjuster").Range("A2")
        Set AdjPx = Sheets("PxAdjuster").Range("L2:O400")
        Set TheCell = Worksheets("DATA").Range("B4:E3000").Offset(0, Column)
        Set DatesC = Sheets("PxAdjuster").Range("A2:A400")
        Set DatesP = Worksheets("DATA").Range("A5")

              For y = 0 To 726 Step 66

                With ActiveSheet.QueryTables.Add(Connection:= _
                    "URL;http://finance.yahoo.com/q/hp?s=" & Symb1 & "&d=5&e=2&f=2008&g=d&a=7&b=20&c=1984&z=" & z & "&y=" & y & "" _
                    , Destination:=Range("A2").Offset(Row, 0))
                    .Name = "Quote: " & Symb1
                    .FieldNames = True
                    .RowNumbers = False
                    .FillAdjacentFormulas = False
                    .PreserveFormatting = True
                    .RefreshOnFileOpen = False
                    .BackgroundQuery = True
                    .RefreshStyle = xlOverwriteCells
                    .SavePassword = False
                    .SaveData = True
                    .AdjustColumnWidth = True
                    .RefreshPeriod = 0
                    .WebSelectionType = xlSpecifiedTables
                    .WebFormatting = xlWebFormattingNone
                    .WebTables = "20"
                    .WebPreFormattedTextToColumns = True
                    .WebConsecutiveDelimitersAsOne = True
                    .WebSingleBlockTextImport = False
                    .WebDisableDateRecognition = False
                    .WebDisableRedirections = False
                    .Refresh BackgroundQuery:=False
                End With

                    Row = Row + 70

                Next y

        aPx.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        DatesP.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        TheCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

   Column = Column + 4

 Next iRow

 End Sub

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Nested For-Each Loop In XSLT dghosh XSLT 10 July 2nd, 2008 04:34 AM
nested loop adnan2 Java Basics 1 July 13th, 2007 04:21 PM
Nested Do Until Loop goels Access VBA 2 September 30th, 2004 08:44 AM
nested while loop doesn't loop hosefo81 PHP Databases 5 November 12th, 2003 08:46 AM

All times are GMT -4. The time now is 09:14 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.