ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] SHEET¾ã¦X

[µo°Ý] SHEET¾ã¦X

½Ð°Ý¤u§@ªí¬OÅܼƤ£ª¾¹D­n«ç»ò§ï»P²¤Æµ{¦¡½X

Sub ¾ã¦X()
'
'
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-6],'ªk¤H±i¼Æ_2019-07-26'!C[-6]:C[-2],5,0)"
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-6],'ªk¤H±i¼Æ_2019-07-26'!C[-6]:C[-2],5,0)),0,(VLOOKUP(RC[-6],'ªk¤H±i¼Æ_2019-07-26'!C[-6]:C[-2],5,0)))"
    Range("H2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-7],'ªk¤H±i¼Æ_2019-07-25'!C[-7]:C[-3],5,0)),0,(VLOOKUP(RC[-7],'ªk¤H±i¼Æ_2019-07-25'!C[-7]:C[-3],5,0)))"
    Range("I2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-8],'ªk¤H±i¼Æ_2019-07-24'!C[-8]:C[-4],5,0)),0,(VLOOKUP(RC[-8],'ªk¤H±i¼Æ_2019-07-24'!C[-8]:C[-4],5,0)))"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-9],'ªk¤H±i¼Æ_2019-07-23'!C[-9]:C[-5],5,0)),0,(VLOOKUP(RC[-9],'ªk¤H±i¼Æ_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],'ªk¤H±i¼Æ_2019-07-22'!C[-10]:C[-6],5,0)),0,(VLOOKUP(RC[-10],'ªk¤H±i¼Æ_2019-07-22'!C[-10]:C[-6],5,0)))"
    Range("L2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-11],'ªk¤H±i¼Æ_2019-07-19'!C[-11]:C[-7],5,0)),0,(VLOOKUP(RC[-11],'ªk¤H±i¼Æ_2019-07-19'!C[-11]:C[-7],5,0)))"
    Range("M2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-12],'ªk¤H±i¼Æ_2019-07-18'!C[-12]:C[-8],5,0)),0,(VLOOKUP(RC[-12],'ªk¤H±i¼Æ_2019-07-18'!C[-12]:C[-8],5,0)))"
    Range("N2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-13],'ªk¤H±i¼Æ_2019-07-17'!C[-13]:C[-9],5,0)),0,(VLOOKUP(RC[-13],'ªk¤H±i¼Æ_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],'ªk¤H±i¼Æ_2019-07-15'!C[-14]:C[-10],5,0)),0,(VLOOKUP(RC[-14],'ªk¤H±i¼Æ_2019-07-15'!C[-14]:C[-10],5,0)))"
    Range("P2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-15],'ªk¤H±i¼Æ_2019-07-12'!C[-15]:C[-11],5,0)),0,(VLOOKUP(RC[-15],'ªk¤H±i¼Æ_2019-07-12'!C[-15]:C[-11],5,0)))"
    Range("Q2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-16],'ªk¤H±i¼Æ_2019-07-11'!C[-16]:C[-12],5,0)),0,(VLOOKUP(RC[-16],'ªk¤H±i¼Æ_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],'ªk¤H±i¼Æ_2019-07-10'!C[-17]:C[-13],5,0)),0,(VLOOKUP(RC[-17],'ªk¤H±i¼Æ_2019-07-10'!C[-17]:C[-13],5,0)))"
    Range("S2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-18],'ªk¤H±i¼Æ_2019-07-09'!C[-18]:C[-14],5,0)),0,(VLOOKUP(RC[-18],'ªk¤H±i¼Æ_2019-07-09'!C[-18]:C[-14],5,0)))"
    Range("T2").Select
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(VLOOKUP(RC[-19],'ªk¤H±i¼Æ_2019-07-08'!C[-19]:C[-15],5,0)),0,(VLOOKUP(RC[-19],'ªk¤H±i¼Æ_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 = "¶R½æ¶W¤Ñ¼Æ"
    Range("U2").Select
    Selection.AutoFill Destination:=Range("U2:U1911")
    Range("U2:U1911").Select
End Sub

¤T¤jªk¤H¾ã¦X.rar (890.79 KB)

§Aª½±µ§â§A·Q­nªº¥\¯à»¡¥X¨Ó ©Î §e²{¥X¨Ó§a

TOP

¨Ì¾ÚSHEETÃÒ¨÷½s½XAÄæ¦ì¤ñ¹ï¥~¸ê¶R½æ¶W
³o¬O§Ú²¤Æµ{¦¡½Xµ²ªG¦ý¤ñ¹ï¸ê®Æ¿ù»~

Sub ¾ã¦X()
'
    Sheets("¤u§@ªí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("¤u§@ªí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
¥¼©R¦W.jpg

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD