[發問] SHEET整合

請問工作表是變數不知道要怎麼改與簡化程式碼

Sub 整合()
'
'
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'法人張數_2019-07-26'!C[-6]:C[-2],5,0)"
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-6],'法人張數_2019-07-26'!C[-6]:C[-2],5,0)),0,(VLOOKUP(RC[-6],'法人張數_2019-07-26'!C[-6]:C[-2],5,0)))"
Range("H2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-7],'法人張數_2019-07-25'!C[-7]:C[-3],5,0)),0,(VLOOKUP(RC[-7],'法人張數_2019-07-25'!C[-7]:C[-3],5,0)))"
Range("I2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-8],'法人張數_2019-07-24'!C[-8]:C[-4],5,0)),0,(VLOOKUP(RC[-8],'法人張數_2019-07-24'!C[-8]:C[-4],5,0)))"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-9],'法人張數_2019-07-23'!C[-9]:C[-5],5,0)),0,(VLOOKUP(RC[-9],'法人張數_2019-07-23'!C[-9]:C[-5],5,0)))"
Range("J2").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Range("K2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-10],'法人張數_2019-07-22'!C[-10]:C[-6],5,0)),0,(VLOOKUP(RC[-10],'法人張數_2019-07-22'!C[-10]:C[-6],5,0)))"
Range("L2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-11],'法人張數_2019-07-19'!C[-11]:C[-7],5,0)),0,(VLOOKUP(RC[-11],'法人張數_2019-07-19'!C[-11]:C[-7],5,0)))"
Range("M2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-12],'法人張數_2019-07-18'!C[-12]:C[-8],5,0)),0,(VLOOKUP(RC[-12],'法人張數_2019-07-18'!C[-12]:C[-8],5,0)))"
Range("N2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-13],'法人張數_2019-07-17'!C[-13]:C[-9],5,0)),0,(VLOOKUP(RC[-13],'法人張數_2019-07-17'!C[-13]:C[-9],5,0)))"
Range("N3").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Range("O2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-14],'法人張數_2019-07-15'!C[-14]:C[-10],5,0)),0,(VLOOKUP(RC[-14],'法人張數_2019-07-15'!C[-14]:C[-10],5,0)))"
Range("P2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-15],'法人張數_2019-07-12'!C[-15]:C[-11],5,0)),0,(VLOOKUP(RC[-15],'法人張數_2019-07-12'!C[-15]:C[-11],5,0)))"
Range("Q2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-16],'法人張數_2019-07-11'!C[-16]:C[-12],5,0)),0,(VLOOKUP(RC[-16],'法人張數_2019-07-11'!C[-16]:C[-12],5,0)))"
Range("Q3").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Range("R2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-17],'法人張數_2019-07-10'!C[-17]:C[-13],5,0)),0,(VLOOKUP(RC[-17],'法人張數_2019-07-10'!C[-17]:C[-13],5,0)))"
Range("S2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-18],'法人張數_2019-07-09'!C[-18]:C[-14],5,0)),0,(VLOOKUP(RC[-18],'法人張數_2019-07-09'!C[-18]:C[-14],5,0)))"
Range("T2").Select
ActiveSheet.Paste
ActiveCell.FormulaR1C1 = _
      "=IF(ISERROR(VLOOKUP(RC[-19],'法人張數_2019-07-08'!C[-19]:C[-15],5,0)),0,(VLOOKUP(RC[-19],'法人張數_2019-07-08'!C[-19]:C[-15],5,0)))"
Range("T3").Select
ActiveWindow.ScrollColumn = 6
Range("G2:T2").Select
Selection.AutoFill Destination:=Range("G2:T1911")
Range("G2:T1911").Select
      ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-15]:RC[-1],"">0"")"
Range("U1").Select
ActiveCell.FormulaR1C1 = "買賣超天數"
Range("U2").Select
Selection.AutoFill Destination:=Range("U2:U1911")
Range("U2:U1911").Select
End Sub

三大法人整合.rar (890.79 KB)

樓主熱帖

zheng211016

　暱稱: zheng逸

中學生 Rank: 2

帖子: 99
主題: 15
精華: 0
積分: 164
點名: 2
作業系統: WIN7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2017-5-30
最後登錄: 2024-10-5

2^#

發表於 2019-7-30 11:21 | 只看該作者

你直接把你想要的功能說出來或呈現出來吧

TOP

BANK870

中學生 Rank: 2

帖子: 9
主題: 5
精華: 0
積分: 63
點名: 0
作業系統: WINDONW
軟體版本: 8
閱讀權限: 20
性別: 男
註冊時間: 2014-12-13
最後登錄: 2024-1-3

3^#

發表於 2019-7-31 02:32 | 只看該作者

依據SHEET證卷編碼A欄位比對外資買賣超
這是我簡化程式碼結果但比對資料錯誤

Sub 整合()
'
Sheets("工作表1").Select
Range("c4").Select
For i = 1 To 15
Sheets(i).Select
Range("e4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("工作表1").Select
ActiveSheet.Paste
'Selection.End(xlRight).Select
ActiveCell.Offset(0, 1).Select
Next
Range("R4").Select
ActiveCell.FormulaR1C1 = "=COUNTIF(RC[-15]:RC[-1],"">0"")"

End Sub

未命名.jpg (321.42 KB)

TOP