- 帖子
- 2035
- 主題
- 24
- 精華
- 0
- 積分
- 2031
- 點名
- 0
- 作業系統
- Win7
- 軟體版本
- Office2010
- 閱讀權限
- 100
- 性別
- 男
- 註冊時間
- 2012-3-22
- 最後登錄
- 2024-2-1
|
回復 5# chairles59
茲將 GBKEE 版大給你的提示整理並稍加修正，
其實 GBKEE 版大已經指引出你的問題所在。- Sub Ex()
- Dim po As Integer ' 宣告 PO 為整數
-
- lR = Range("A2").End(xlDown).Row ' R = 9 : Variant/Long
- Rows(lR + 1 & ":400").Clear ' 直接清除儲存格,不必 Select 再刪除
- ' Selection.Delete shift:=xlUp
-
- LRA = Range("B2").End(xlDown).Row ' LRA = 9 : Variant/Long
- For i = 3 To LRA
- ' If Cells(i, 3) <> "" Then ' 工作表 C 欄 ,沒有買進日期,所以不會執行匯入資料的程式碼
- If Cells(i, 3) = "" Then ' 修正為空值時始進行資料匯入
- Valuesno = "$A$" & i
- Linkss = "URL;https://tw.stock.yahoo.com/q/q?s=" & Cells(i, 1)
-
- po = lR - 5 + (7 * (i - 2)) ' 定抓取資料表格迴圈
- With ActiveSheet.QueryTables.Add(Connection:= _
- Linkss, Destination:=ActiveSheet.Range("B" & po))
- .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 = "6"
- .WebPreFormattedTextToColumns = True
- .WebConsecutiveDelimitersAsOne = True
- .WebSingleBlockTextImport = False
- .WebDisableDateRecognition = False
- .WebDisableRedirections = False
- .Refresh BackgroundQuery:=False
- .Name = .ResultRange.Cells(3, 1)
- End With
-
- Range("A" & po + 2) = Cells(i, 1)
- ' Cells(i, 2) = "=vlookup(" & Cells(i, 1) & ",$A$16:$O$200,2,0)" ' 是英文字母 O,不是數字 0
- Cells(i, 2) = "=vlookup(" & Cells(i, 1) & ",$A$11:$O$200,2,0)" ' 從 $A$16 起始之範圍會造成 B3 得出不正確值
- Cells(i, 2) = Mid(Cells(i, 2).Text, 5) ' Mid(Cells(i, 2), 5) 會產生執行階段錯誤 13 型態不符
-
- ' Cells(i, 7) = "=vlookup(" & Cells(i, 1) & ",$A$16:$O$200,4,0)" ' 是英文字母 O,不是數字 0
- Cells(i, 7) = "=vlookup(" & Cells(i, 1) & ",$A$11:$O$200,4,0)" ' 從 $A$16 起始之範圍會造成 G3 得出不正確值
- End If
- Next
- End Sub
|
|
發表於 2016-3-14 10:07
| 
