|
Subject:
|
QueryTables nested loop
|
|
Posted By:
|
Sherman McCoy
|
Post Date:
|
6/4/2008 2:37:01 PM
|
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
|
|
