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

2 ­Ó ON ERROR GOTO ERROR ¥X²{¿ù»~

2 ­Ó ON ERROR GOTO ERROR ¥X²{¿ù»~

¥»©«³Ì«á¥Ñ mdr0465 ©ó 2020-10-3 17:52 ½s¿è

¦U¦ì¤j¤j§A¦n

§Ú¦b"L"Ä檺Àx¦s®æ, ¥ÎVLOOKUP¦b "2019 SOC NO."  ¨½E ªº¸ê®Æ·j´M"¦¨¥»"ªºDÄæ, ¦ý¦pªG¦¨¥»¨S¦³´N·|¦b"´Á³f" ¦pªG¦A¨S¦³§ä¨ì´N·|Åã¥ÜªÅ®æ,

¦p¦¨¥\§ä¨ì°t¹ï´N·|¦b"K"Äæµù©ú¦b¨º¤@­Ó¬¡Á¡­¶§ä¨ì°t¹ï

¦ý§Ú¥Î2 ­Ó ON ERROR GOTO ERROR1  ,ON ERROR GOTO ERROR 2, ´N·|¥X²{°ÝÃD

½Ð¦U¦ìÀ°¦£¬Ý¬Ý§Ú¨º¨½¥X¿ù, ÁÂÁÂ

¦pªí¹F±o­ø¦n,½Ð¨£½Ì

ON ERROR GOTO.rar (499.94 KB)

­Ó¤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. error1:
  12.     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)
  13.     If Err Then Err.Clear: GoTo error2
  14.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "´Á³fªí"
  15.    
  16. error2:
  17.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = ""
  18.     If Err Then MsgBox "Error has found"

  19. Application.Calculation = xlCalculationAutomatic
  20. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# ikboy

IKBOY ÁÂÁ§Aªº¦^ÂÐ,
¦ý·í¤¤VLOOKUP ¬O¦³°t¹ï¦¨¥\ªº, ¦ý¬O¤½¦¡¬O¨S¦³¸õ¨ì07,08,09¬qªº¤½¦¡, ½Ð°Ý§ÚÀ³¦p¦ó­×§ï©O??

ÁÂÁÂÀ°¦£

TOP

­º¥ý§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. 'On Error GoTo error1
  10. Worksheets("2019 TRADING SOC NO.").Select
  11. For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
  12.     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)
  13.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "¦¨¥»ªí"
  14.     Next
  15. error1:
  16. If Not d.exists("2019 TRADING SOC NO.") Then GoTo error2
  17. 'On Error GoTo error2
  18.     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)
  19.     Worksheets("2019 TRADING SOC NO.").Range("M" & i).Value = "´Á³fªí"
  20. error2:
  21.     If Not d.exists("2019 TRADING SOC NO.") Then GoTo 1000
  22.     Worksheets("2019 TRADING SOC NO.").Range("L" & i).Value = ""
  23. 1000
  24. Application.Calculation = xlCalculationAutomatic
  25. End Sub
½Æ»s¥N½X

TOP

¦^´_ 4# ikboy

Ikboy
ÁÂÁ§Aªº¦^ÂСA

TOP

¦^´_ 5# mdr0465

°a¤ªºµ®v¥S,

½Ð¬d¾\,ÁÂÁÂÀ°¦£

ÁÂÁÂ

ON ERROR GOTO.rar (383.02 KB)

TOP

ÀÉ®×µLªk¶}, ¤j¬ù§ì¤@¤U
Sub TEST_A01()
Dim Sht As Worksheet, S1, S2, TC, M(2)
On Error Resume Next
Set Sht = Sheets("2019 TRADING SOC NO.")
If Sht Is Nothing Then MsgBox "¤u§@ªí¡e2019 TRADING SOC NO.¡f¤£¦s¦b!  ": Exit Sub
On Error GoTo 0
'------------------------------
For i = 2 To Sht.Range("A" & Rows.Count).End(xlUp).Row
    S1 = "":  S2 = "":  TC = Sht.Range("E" & i).Value
    On Error Resume Next
    M(1) = Application.VLookup(TC, Sheets("¦¨¥» (2020.09.28)").Range("D:AL"), 35, 0)
    M(2) = Application.VLookup(TC, Sheets("´Á³fÁ`¶× (2020.09.16)").Range("A:G"), 7, 0)
    On Error GoTo 0
    For j = 1 To 2
        If Not IsError(M(j)) Then S1 = M(j): S2 = Array("¦¨¥»ªí", "´Á³fªí")(j - 1): Exit For
    Next j
    Sht.Range("L" & i) = S1
    Sht.Range("M" & i) = S2
Next i
End Sub


'===============================

TOP

¦^´_ 6# mdr0465

¤£ª¾¹D ¬O¤£¬O§A­nªº  §Ú¦³§â·Ç¤j¼gªº ©ñ¤W¥h ¦pªG¤£¬O ½Ð§i¶D§Ú°ÝÃD¥X¦b­þ¸Ì ÁÂÁ §Ú¤]¬O¶¶«K½m²ß

javascript:;

1009.rar (506.45 KB)

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-10-10 00:37 ½s¿è

¦^´_ 6# mdr0465
§Ú§â¦ì¸m§ï¤F¤@¤U ¦]¬° µ²ªG¸ò·Ç¤jªº¤£¤@¼Ë ©Ò¥H§ï¤F¤@¤U §A¦A¬Ý¬Ý§a


javascript:;

1009_01.rar (505.63 KB)

TOP

¦^´_ 7# ­ã´£³¡ªL

­ã´£³¡ªLª©¥D
³o´N¬O§Ú·Q­nªºªí¹F¤è¦¡
¸U¤À·PÁ§AªºÀ°¦£
   
ÁÂÁÂ

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD