Question on Destination Cells of VBA

[JStellato]

Hello, I need help with some code. What I have is an extremely simple worksheet. Column A has Stock Tickers, my Code Goes out and grabs the Prices for the Tickers, and I want to put the Price for the Stock in the adjoining Column B of the Worksheet. However, right now the values return fine, but go across columns instead of down rows.

Sub GetStockQuotes()

Dim DataRange As Range
Dim Cell As Range
Dim LastRow As Long
Dim StartRow As Long
Dim Col As Long
Dim Ticker As String
Dim StockPrice As Long

StartRow = 1
Col = 1

LastRow = FindLastRow(Col)
Set DataRange = Range(Cells(StartRow, Col), Cells(LastRow, Col))

For Each Cell In DataRange

Ticker = Cell.Value
ConnectStr = "URL;http://finance.yahoo.com/d/quotes.csv?s=" & Ticker & "&f=l1"
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=Range("B65536").End(xlUp))
    .Name = "StockPrices_" & StSymbol
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SaveData = True
    .AdjustColumnWidth = True
    .WebSelectionType = xlSpecifiedTables
    .WebTables = "20"
    .WebFormatting = xlWebFormattingNone
    .WebSingleBlockTextImport = False
    .Refresh
End With

Next Cell

End Sub

Function FindLastRow(ByVal Col As Integer) As Long
FindLastRow = Cells(Rows.Count, Col).End(xlUp).Row
End Function

 

[AlphaFrog]

Sub GetStockQuotes()

Dim DataRange As Range
Dim cell As Range
Dim LastRow As Long
Dim StartRow As Long
Dim Col As Long
Dim Ticker As String
Dim StockPrice As Long

StartRow = 1
Col = 1

LastRow = FindLastRow(Col)
Set DataRange = Range(Cells(StartRow, Col), Cells(LastRow, Col))

For Each cell In DataRange

Ticker = cell.Value
ConnectStr = "URL;http://finance.yahoo.com/d/quotes.csv?s=" & Ticker & "&f=l1"
With ActiveSheet.QueryTables.Add(Connection:=ConnectStr, Destination:=[COLOR="Red"]cell.Offset(, 1)[/COLOR])
.Name = "StockPrices_" & StSymbol
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = [COLOR="Red"]xlOverwriteCells[/COLOR]
.SaveData = False
.AdjustColumnWidth = True
.WebSelectionType = xlSpecifiedTables
.WebTables = "20"
.WebFormatting = xlWebFormattingNone
.WebSingleBlockTextImport = False
.Refresh
End With

Next cell

End Sub

Function FindLastRow(ByVal Col As Integer) As Long
FindLastRow = Cells(Rows.Count, Col).End(xlUp).Row
End Function

[COLOR="Red"]Sub Refresh_Stocks()
    Dim cell As Range
    On Error Resume Next
    For Each cell In Range("B1", Range("B" & Rows.Count).End(xlUp))
        cell.QueryTable.Refresh
    Next cell
End Sub[/COLOR]

 

[JStellato]

Wow, thanks a lot, that worked perfectly. I have a related question but I'll start a new thread for that.