Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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
 
Old June 4th, 2008, 02:37 PM
Registered User
 
Join Date: Jun 2008
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


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


   Column = Column + 4

 Next iRow

 End Sub






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





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