What a stupid mistake. Thanks a lot!
Do you additionally know why I get a 1004 error on this code sometimes? Debug gives an error on this line .Refresh BackgroundQuery:=False
I solved it by turning on Option explicit. NO idea why that works though.
Sub SelectCell()
Dim darget As Range
Set darget = Sheets("Blad1").Range("I12")
Application.ScreenUpdating = False
For i = 12 To 50
Call Getprice((Sheets("Blad1").Range("D" & i).Text), darget)
Set darget = darget.Cells(2, 1)
Next i
Sheets("Blad1").Select
' Range("F5:F40").Select
'Selection.Replace What:=".", Replacement:=",", LookAt:=xlPart, _
' SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
' ReplaceFormat:=False
' Selection.NumberFormat = "0.00"
Application.ScreenUpdating = True
MsgBox ("Done!")
End Sub
Sub Getprice(Mystr, darget)
Sheets("Macroveld").Select
ConnectString = "URL;http://finance.yahoo.com/q?s=" & Mystr
With ActiveSheet.QueryTables.Add(Connection:= _
ConnectString, Destination:=Range("$A$3"))
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "14"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Range("B3").Select
Selection.Copy
Sheets("Blad1").Select
darget.Select
ActiveSheet.Paste
Sheets("Macroveld").Select
Range("A1:e14").Select
Application.CutCopyMode = False
Selection.QueryTable.Delete
Selection.ClearContents
End Sub
Bookmarks