麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] SHEET整合 [打印本頁]

作者: BANK870 時間: 2019-7-28 20:15 標題: 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

作者: zheng211016 時間: 2019-7-30 11:21

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

作者: BANK870 時間: 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

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)