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

flutist

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

test.rar (2.45 KB)

¤U¸ü¦¸¼Æ: 27

2012-12-11 15:32

freeffly

¥H«e100²Õ100¸U®É¦³¬Ý¹L¤H®a¥Î¨ç¼Æ·f°t²³æªºvba
¤£¹L¥D­n¬O¥H¨ç¼Æ¬°¥D
²Ä¤@¦¸¬Ý¨ì¥Îvba¹ï¼úªº

GBKEE

¦^´_ 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

flutist

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

Hsieh

¦^´_ 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

stillfish00

À³¸Ó¬OCase 60, 61  

stillfish00

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

stillfish00

¦^´_ 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