ikboy

­Ó¤H«ØijÀ³ºÉ¥i¯à¤£¨Ï¥Î On Error..

1. Sub crosscheck_new_item()
   
2. Application.Calculation = xlCalculationManual
   
3. Dim i As Long
   
4. On Error Resume Next
   
5. Worksheets("2019 TRADING SOC NO.").Select
   
6. If Err Then Err.Clear: GoTo error1
   
7. For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
   
8.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = WorksheetFunction.VLookup(Worksheets("2019 TRADING SOC NO.").Range("E" & i).Value, Worksheets("¦¨¥» (2020.09.28)").Range("D:AL"), 35, 0)
   
9.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "¦¨¥»ªí"
   
10.     Next
    
11. 
    
12. error1:
    
13.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = WorksheetFunction.VLookup(Worksheets("2019 TRADING SOC NO.").Range("E" & i).Value, Worksheets("´Á³fÁ`¶× (2020.09.16)").Range("A:G"), 7, 0)
    
14.     If Err Then Err.Clear: GoTo error2
    
15.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "´Á³fªí"
    
16.    
    
17. error2:
    
18.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = ""
    
19.     If Err Then MsgBox "Error has found"
    
20. 
    
21. Application.Calculation = xlCalculationAutomatic
    
22. End Sub

½Æ»s¥N½X

ikboy

­º¥ý§Aªºµ{¦¡½X Worksheets("2019 TRADING SOC NO.") ¸ò¥»¤£¦s¦b
On Error Resume Next...¥\¯à¬OÅýµ{¦¡¤£²z·|¿ù»~Ä~Äò¶]¤U¥h, On Error Goto ..¶]¨ì«ü©w¦æÄ~Äò¶]

1. Sub crosscheck_new_item()
   
2. Application.Calculation = xlCalculationManual
   
3. Dim i As Long, d As Object
   
4. Set d = CreateObject("scripting.dictionary")
   
5. For Each s In Sheets
   
6.     d(s) = "" '³Ð«Ø¦r¨å¦¬®ð©Ò¦³­¶¦W
   
7. Next
   
8. If Not d.exists("2019 TRADING SOC NO.") Then GoTo error1    '¦pªG­¶¦W"2019 TRADING SOC NO."¤£¦s¦b¶]¨ì«ü©w¦æÄ~Äò¶]
   
9. 
   
10. 'On Error GoTo error1
    
11. Worksheets("2019 TRADING SOC NO.").Select
    
12. For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
    
13.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = WorksheetFunction.VLookup(Worksheets("2019 TRADING SOC NO.").Range("E" & i).Value, Worksheets("¦¨¥» (2020.09.28)").Range("D:AL"), 35, 0)
    
14.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "¦¨¥»ªí"
    
15.     Next
    
16. error1:
    
17. If Not d.exists("2019 TRADING SOC NO.") Then GoTo error2
    
18. 'On Error GoTo error2
    
19.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = WorksheetFunction.VLookup(Worksheets("2019 TRADING SOC NO.").Range("E" & i).Value, Worksheets("´Á³fÁ`¶× (2020.09.16)").Range("A:G"), 7, 0)
    
20.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "´Á³fªí"
    
21. error2:
    
22.     If Not d.exists("2019 TRADING SOC NO.") Then GoTo 1000
    
23.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = ""
    
24. 1000
    
25. Application.Calculation = xlCalculationAutomatic
    
26. End Sub

½Æ»s¥N½X