What's wrong with this? SUGGESTIONS PLEASE!!!!

[mjansen]

Here's my script... but it errors out:
Sub dow()

Sheets("Indices").Select
Application.ScreenUpdating = False
Dim Sh As Worksheet
Dim Rng As Range
Dim Cell As Range
Dim Ticker As String
Dim StartDate As Date
Dim EndDate As Date
Dim a, b, c, d, e, f
Dim StrURL As String
Set Sh = Worksheets("Template")
Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlUp).Row)
For Each Cell In Rng
Ticker = Cell.Value
StartDate = Cell.Offset(0, 1).Value
EndDate = Cell.Offset(0, 2).Value
a = Format(Month(StartDate) - 1, "00") ' Month minus 1
b = Day(StartDate)
c = Year(StartDate)
d = Format(Month(EndDate) - 1, "00")
e = Day(EndDate)
f = Year(EndDate)
StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
StrURL = StrURL & "s=^DJI&a=" & a & "&b=" & b
StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
StrURL = StrURL & "&f=" & f & "&g=w&ignore=.csv"
Sheets("Indices").Select
With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1))
Range("A2").Select
Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
Columns("A:G").EntireColumn.AutoFit
Range("H1").Select
ActiveCell.FormulaR1C1 = "Growth"
Dim LC As Range
Dim rw, x As Integer
Set LC = Range("H65536").End(xlUp)
rw = LC.Row
For x = 2 To LC.Row
Cells(x, 19) = Cells(x, 18) / LC.Value - 1
Next Sheets("TopSheet")
End Sub

Thnaks for any help!

Marc

 

[zilpher]

"It errors out." Any clues where?

Z

 

[mjansen]

It errors out at Next Sheets("TopSheet") very last line before End Sub.

But I don't understand why....

 

[mjansen]

bump

 

[MartinK]

Where is the For of this Next?
Is it For Each cl In Rng?

Then change this line to
Next cl

What do you want to achieve by next Sheets("..")?
Martin

 

[MartinK]

You have two For's and just one Next.

You need to add these:

next x
next cl

 

[brettvba]

Nothings wrong just need to move that last part

Sub dow()

Sheets("Indices").Select
Application.ScreenUpdating = False
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Ticker As String
    Dim StartDate As Date
    Dim EndDate As Date
    Dim a, b, c, d, e, f
    Dim StrURL As String
    Set Sh = Worksheets("Template")
    Set Rng = Sh.Range("A2:A" & Sh.Range("A2").End(xlUp).Row)
    For Each Cell In Rng
        Ticker = Cell.Value
        StartDate = Cell.Offset(0, 1).Value
        EndDate = Cell.Offset(0, 2).Value
        a = Format(Month(StartDate) - 1, "00") ' Month minus 1
        b = Day(StartDate)
        c = Year(StartDate)
        d = Format(Month(EndDate) - 1, "00")
        e = Day(EndDate)
        f = Year(EndDate)
        StrURL = "URL;http://table.finance.yahoo.com/table.csv?"
        StrURL = StrURL & "s=^DJI&a=" & a & "&b=" & b
        StrURL = StrURL & "&c=" & c & "&d=" & d & "&e=" & e
        StrURL = StrURL & "&f=" & f & "&g=w&ignore=.csv"
        Sheets("Indices").Select
        With ActiveSheet.QueryTables.Add(Connection:=StrURL, Destination:=Range("A1"))
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .WebSelectionType = xlAllTables
            .WebFormatting = xlWebFormattingNone
            .WebPreFormattedTextToColumns = True
            .WebConsecutiveDelimitersAsOne = True
            .WebSingleBlockTextImport = False
            .WebDisableDateRecognition = False
            .Refresh BackgroundQuery:=False
        End With
        Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1))
        Range("A2").Select
        Range(Selection, Selection.End(xlDown)).NumberFormat = "d-mmm-yy"
        Columns("A:G").EntireColumn.AutoFit
        Range("H1").Select
        ActiveCell.FormulaR1C1 = "Growth"
        Dim LC As Range
        Dim rw, x As Integer
        Set LC = Range("H65536").End(xlUp)
        rw = LC.Row
    Next
    For x = 2 To LC.Row
        Cells(x, 19) = Cells(x, 18) / LC.Value - 1
    Next
    'Sheets("TopSheet")
End Sub

Not sure what your doing with Sheets("TopSheet") are you trying to select it as you can't have next Sheets("TopSheet")!

Brett

 

[mjansen]

I used that, but now i get a runtime error : mismatch at the line startdate =


Thanks for the suggestions so far,

marc

 

[brettvba]

Is the value in the cell that you are trying to assign the StartDate to a valid date?

Brett

 

[mjansen]

yes.... maybe if i give send you the whole script it might help? Or atleast an explanation of what i thought everything is supposed to do?


Thanks again for your prompt help,


Marc