- 帖子
- 9
- 主題
- 5
- 精華
- 0
- 積分
- 63
- 點名
- 0
- 作業系統
- WINDONW
- 軟體版本
- 8
- 閱讀權限
- 20
- 性別
- 男
- 註冊時間
- 2014-12-13
- 最後登錄
- 2024-1-3
|
請問工作表是變數不知道要怎麼改與簡化程式碼
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)
|
發表於 2019-7-28 20:15
| 
