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

³]­p¤@­Ó¹ï¼úµ{¦¡

³]­p¤@­Ó¹ï¼úµ{¦¡

½Ð°Ý¦p¦ó³]­p¤@­Ó¹ï¼úµ{¦¡?§Ú¦³ªþ¥ó

test.rar (2.45 KB)

¦^´_ 1# flutist
ÆZ°½Ãiªº¼gªk...
  1. Sub LottoRun()
  2.     With Sheets("Sheet1").[D2]
  3.     Select Case [=IFERROR(SUM(COUNTIF(Sheet1!B2:B6,OFFSET(Sheet2!B1:G1,MATCH(Sheet1!A2,Sheet2!A:A,0)-1,)))*10+(B7=OFFSET(Sheet2!H1,MATCH(Sheet1!A2,Sheet2!A:A,0)-1,)),-1)]
  4.         Case 61
  5.             .Value = "®¥³ß§A¹ï¤¤ÀY¼ú"
  6.         Case 51
  7.             .Value = "®¥³ß§A¹ï¤¤¶L¼ú"
  8.         Case 50
  9.             .Value = "®¥³ß§A¹ï¤¤°Ñ¼ú"
  10.         Case 41
  11.             .Value = "®¥³ß§A¹ï¤¤¸v¼ú"
  12.         Case 40
  13.             .Value = "®¥³ß§A¹ï¤¤¥î¼ú"
  14.         Case 31
  15.             .Value = "®¥³ß§A¹ï¤¤³°¼ú¡A¼úª÷$1,000"
  16.         Case 30
  17.             .Value = "®¥³ß§A¹ï¤¤´¶¼ú¡A¼úª÷$400"
  18.         Case -1
  19.             .Value = "´Á§O§ä¤£¨ì"
  20.         Case Else
  21.             .Value = "´Ý©À"
  22.     End Select
  23.     .EntireColumn.AutoFit
  24.     End With
  25. End Sub
½Æ»s¥N½X

TOP

§ó¥¿ ÀY¼ú¬°Case 60

TOP

À³¸Ó¬OCase 60, 61  

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2012-12-11 18:30 ½s¿è

¦^´_ 1# flutist
°ò¥»¤W¨Ï¥Î¤½¦¡§Y¥i¹F¦¨¡A¼g¦¨VBA³Ì¦n°t¦X¨Æ¥óµ{§Ç
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. If Intersect(Target, [B3:B7]) Is Nothing Then Exit Sub
  3. If Application.CountIf(Sheet2.[A:A], Sheet1.[A2]) = 0 Then Sheet1.[D2] = "´Á§O¿ù»~": Exit Sub '´Á§O¿ù»~
  4. '°ò¥»¸¹½X
  5. x = _
  6. Evaluate("=SUMPRODUCT(ISNUMBER(1/COUNTIF(OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$2,Sheet2!$A:$A,0)-1,1,,6),Sheet1!$B$2:$B$7))*1)")
  7. '¯S§O¸¹
  8. y = _
  9. Evaluate("=ISNUMBER(MATCH(OFFSET(Sheet2!$A$1,MATCH(Sheet1!$A$2,Sheet2!$A:$A,0)-1,7),Sheet1!$B$2:$B$7,0))*0.5")
  10. Sheet1.[D2] = Application.Lookup(x + y, Array(0, 3, 3, 5, 4, 4.5, 5, 5.5, 6), Array("¥¼¤¤¼ú", "´¶¼ú", "³°¼ú", "¥î¼ú", "¸v¼ú", "¤T¼ú", "¶L¼ú", "ÀY¼ú"))
  11. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¤½¦¡§Ú·|³]­p
¦ýVBA´N¤£¦æ¤F
ÁÂÁ«e½ú«ü¾É

TOP

¦^´_ 6# flutist
  1. Option Explicit
  2. Sub Ex()
  3.     Dim ´Á§O As Range, Msg As String, xi As Integer, xS As Integer
  4.     With Sheets("Sheet1")
  5.         .[B2:B7].Name = "§ëª`°Ï"
  6.         Set ´Á§O = Sheets("sheet2").Range("a:a").Find(.[a2], lookat:=xlWhole)       '´M§ä´Á§O
  7.         Msg = IIf(´Á§O Is Nothing, "´Á    §O:" & .[a2] & " §ä¤£¨ì!!! " & vbLf, "") & _
  8.               IIf([COUNT(§ëª`°Ï)] <> 6, "§ëª`°Ï: ¸¹½X¤£»ô¥þ", "")                   '[COUNT(§ëª`°Ï)] <> 6 §ëª`¸¹½X»Ý¦³6­Ó
  9.         If Msg <> "" Then MsgBox Msg: Exit Sub
  10.         [§ëª`°Ï].Interior.ColorIndex = .[a2].Interior.ColorIndex                    '¨î©w§ëª`°Ï©³¦â
  11.         For xi = 1 To 7
  12.             If IsNumeric(Application.Match(´Á§O.Offset(, xi), [§ëª`°Ï], 0)) Then    '¶}¥X¼ú¸¹¤@¤@¤ñ¹ï   §ëª`¸¹½X
  13.                 If xi < 6 Then
  14.                  xS = xS + 1
  15.                  [§ëª`°Ï].Cells(Application.Match(´Á§O.Offset(, xi), [§ëª`°Ï], 0)).Interior.ColorIndex = 17  '¶}¥X¸¹¸¹:¨î©w©³¦â
  16.                 Else
  17.                   Msg = "OK"        '¯S§O¸¹
  18.                   [§ëª`°Ï].Cells(Application.Match(´Á§O.Offset(, xi), [§ëª`°Ï], 0)).Interior.ColorIndex = 7   '¯S§O¸¹:¨î©w©³¦â
  19.                 End If
  20.             End If
  21.         Next
  22.         With .[D2]
  23.             Select Case xS
  24.                 Case 6
  25.                     .Value = "¡u®¥³ß§A¹ï¤¤ÀY¼ú¡v"
  26.                 Case 5
  27.                     .Value = IIf(Msg <> "", "¡u®¥³ß§A¹ï¤¤¶L¼ú¡v", "¡u®¥³ß§A¹ï¤¤°Ñ¼ú¡v")
  28.                 Case 4
  29.                     .Value = IIf(Msg <> "", "¡u®¥³ß§A¹ï¤¤¸v¼ú¡v", "¡u®¥³ß§A¹ï¤¤¥î¼ú¡v")
  30.                 Case 3
  31.                     .Value = IIf(Msg <> "", "¡u®¥³ß§A¹ï¤¤³°¼ú¡v", "¡u¼úª÷$1,000¡v")
  32.                 Case 2
  33.                     .Value = IIf(Msg <> "", "¡u®¥³ß§A¹ï¤¤´¶¼ú¡v", "¡u¼úª÷$400¡v")
  34.                 Case Else
  35.                 .Value = "¤U´Á¦A¨Ó"
  36.             End Select
  37.         End With
  38.     End With
½Æ»s¥N½X

TOP

¥H«e100²Õ100¸U®É¦³¬Ý¹L¤H®a¥Î¨ç¼Æ·f°t²³æªºvba
¤£¹L¥D­n¬O¥H¨ç¼Æ¬°¥D
²Ä¤@¦¸¬Ý¨ì¥Îvba¹ï¼úªº
¦r¨å¨â¦U¦r ÁÙ¯uÃø²z¸Ñ

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD