Excel Keeps Crashing

[ianfrancis56]

Hi all,

Is there something wrong with this code or a way to improve it? Excel keeps crashing when I try to run it...Thanks!

Sub GetData()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim DwnLoadOK As Boolean
Dim URL$, Dest$, sym$, count@, i@
Dim old As Worksheet, wb As Workbook
Dim lastr As Range, lastrt As Range, ticks As Range, lastrtt As Range


With Workbooks("Symb").Sheets("Tick")
    Set lastr = .Range("A1:A" & .Range("A1").End(xlDown).Row)
End With

i = 1
count = 50
Set wb = ThisWorkbook

Application.DisplayAlerts = False
For Each old In wb.Worksheets
    If old.Name <> "Tick" Then
        old.Delete
    End If
Next old


On Error Resume Next
For Each ticks In lastr
    
    sym = lastr(i, 1).Value
    
    Workbooks("Symb").Worksheets.Add(After:=Sheets _
    (Sheets.count)).Name = sym
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=" _
    & sym & "&d=8&e=26&f=2011&g=d&a=8&b=7&c=1900&ignore=.csv"
    Dest = "C:\Symbols\" & sym & ".csv"
    DwnLoadOK = DownloadFile(URL, Dest)
        
    Workbooks.Open ("C:/Symbols/" & sym & ".csv")
    With Workbooks(sym).Sheets(1)
        Set lastrt = .Range("A1:F" & .Range("F1").End(xlDown).Row)
    End With
    With Workbooks("Symb").Sheets(sym)
        Set lastrtt = .Range("A1:F" & Workbooks(sym).Sheets(1).Range("F1").End(xlDown).Row)
        
        lastrtt.Value = lastrt.Value
     
        Workbooks(sym).Close
        Kill "C:\Symbols\" & sym & ".csv"
                
        .Cells(1, 7) = "ND Open"
        .Cells(1, 8) = "ND Close"
        .Cells(1, 9) = "ND High"
        .Cells(1, 10) = "ND Low"
        .Cells(1, 11) = "ND High R"
        .Cells(1, 12) = "ND Low R"
        .Cells(1, 13) = "ND R"
        .Cells(1, 14) = "R"
        .Cells(3, 7) = "=B2"
        .Cells(3, 8) = "=E2"
        .Cells(3, 9) = "=C2"
        .Cells(3, 10) = "=D2"
        .Cells(3, 11) = "=(C2-B2)/B2"
        .Cells(3, 12) = "=(D2-B2)/B2"
        .Cells(3, 13) = "=(E2-B2)/B2"
        .Cells(2, 14) = "=(E2-B2)/B2"
        .Range("G3:G" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 7).Formula
        .Range("H3:H" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 8).Formula
        .Range("I3:I" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 9).Formula
        .Range("J3:J" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 10).Formula
        .Range("K3:K" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 11).Formula
        .Range("L3:L" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 12).Formula
        .Range("M3:M" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(3, 13).Formula
        .Range("N3:N" & .Range("F3").End(xlDown) _
        .Row).Formula = .Cells(2, 14).Formula
        End With
        
        If count = i Then
        Shell "RunDll32.exe InetCpl.Cpl, ClearMyTracksByProcess 11"
        count = count + 50
        End If
        
        i = i + 1
        
    Next ticks

Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

 

[ianfrancis56]

Got it figured out! I inserted an If statement to check to see if the downloaded file had any data. I think the code was getting hung up trying to copy and past a bajillion blank cells.