VBA Help Needed- List Loop

[jwest100]

Hi,

I cannot get the macro below to go through the entire stock list, just stops after 1 stock ticker, need it to go through all. Range- "StockTicker" contains the dropdown list. Please help.

Option Explicit

Public Const StartRow As Long = 2    'Start data row

Sub GetData()
Dim DataSheet As Worksheet
Dim EndDate As Date
Dim StartDate As Date
Dim Symbol As String
Dim qurl As String
Dim nQuery As Name
Dim LastRow As Integer
Dim c As Range
    
For Each c In Range("StockTicker")


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual

Sheets("Data").Cells.Clear

Set DataSheet = ActiveSheet

StartDate = DataSheet.Range("StartDate").Value
EndDate = DataSheet.Range("EndDate").Value
Symbol = DataSheet.Range("StockTicker").Value
Sheets("Data").Range("a1").CurrentRegion.ClearContents

qurl = "http://ichart.finance.yahoo.com/table.csv?s=" & Symbol
qurl = qurl & "&a=" & Month(StartDate) - 1 & "&b=" & Day(StartDate) & _
       "&c=" & Year(StartDate) & "&d=" & Month(EndDate) - 1 & "&e=" & _
       Day(EndDate) & "&f=" & Year(EndDate) & "&g=" & Sheets("Data").Range("a1") & "&q=q&y=0&z=" & _
       Symbol & "&x=.csv"

QueryQuote:
With Sheets("Data").QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("Data").Range("a1"))
    .BackgroundQuery = True
    .TablesOnlyFromHTML = False
    .Refresh BackgroundQuery:=False
    .SaveData = True
End With

Sheets("Data").Range("a1").CurrentRegion.TextToColumns Destination:=Sheets("Data").Range("a1"), DataType:=xlDelimited, _
                                                       TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
                                                       Semicolon:=False, Comma:=True, Space:=False, other:=False

Sheets("Data").Columns("A:G").ColumnWidth = 12

LastRow = Sheets("Data").UsedRange.Rows.Count

Sheets("Data").Sort.SortFields.Add Key:=Range("A2:A" & LastRow), _
                                   SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With Sheets("Data").Sort
    .SetRange Range("A1:G" & LastRow)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    .SortFields.Clear
End With

Call ShowData
Application.Calculation = xlCalculationAutomatic

    Sheets("Data").Select
    Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
    Dim a As Range
    For Each a In Selection.Cells
    a = Trim(a)
    Next
    
    Dim NextCol As Long
    If Sheets("Data Set").Range("A1").Value = "" Then
        NextCol = 1
    Else
        NextCol = Sheets("Data Set").Cells(1, Columns.Count).End(xlToLeft).Column + 1
    End If
   
    ActiveSheet.Range("A1:W" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
    Sheets("Data Set").Select
    Cells(1, NextCol).PasteSpecial xlValues
    Application.CutCopyMode = False
    
    Next c
    
End Sub

 

[Rijnsent]

Hi jwest100,

I assume your range "stockticker" contains various stock tickers in a list?

The basic loop you'll then need is something like this. Note the .Cells and the c.Value (compared to your code).

Cheers,

Koen

Sub TestLoop()

Set DataSheet = ActiveSheet
For Each c In Range("StockTicker").Cells
    Symbol = c.Value
Next c

End Sub

 

[tonyyy]

If the range StockTicker is a single cell with a dropdown validation list, then your code is working as designed. There are at least two approaches to loop through the entire list:

1. Change the StockTicker range so it matches the list of cells that are used to create the data validation list (assuming you created the list from a range of cells), or...
2 Loop through the data validation list, such as...

Sub loopthroughvalidationlist()
     Dim inputRange As Range
     Dim c As Range
     Set inputRange = Evaluate(Range("StockTicker").Validation.Formula1)
     For Each c In inputRange
        '... do something with c.Value
    Next c
End Sub

Cheers,

tonyyy