[µo°Ý] VBA ªí®æ¤º¬Y®æ¼Æ­Èµ¥©ó¨ä¥LEXCELÀÉ VBA, ¨ä¥LÀÉ®×

toxin

1. Private Sub CommandButton1_Click()
   
2.     Dim a As Integer, i As Integer
   
3.     Dim b As Integer
   
4.     Dim c As Variant
   
5.     Dim d As Variant
   
6.     Dim E As Variant
   
7.     IA = 7000
   
8.     IB = 3
   
9.     IC = 10
   
10.    
    
11. 
    
12.    
    
13.    Do
    
14.       If Sheets("»s³y³æ").Range("E" & IB) <> "" Then
    
15.       IB = IB + 1
    
16.       End If
    
17.       Loop While Sheets("»s³y³æ").Range("E" & IB) <> ""
    
18. 
    
19.       
    
20.    
    
21. AGO:
    
22. 
    
23. 
    
24. IC = IC + 1
    
25. Do
    
26. 
    
27.    
    
28.     d = Sheets("»s³y³æ").Range("D" & IB)
    
29.     Set c = Sheets("«È¤á").Range("B11:B" & IC).Find(What:=d, LookIn:=xlFormulas, _
    
30.          LookAt:=1, SearchOrder:=2, SearchDirection:=xlNext, _
    
31.          MatchCase:=False, MatchByte:=False, SearchFormat:=True)
    
32.     If IC = IA Then
    
33.     Sheets("»s³y³æ").Range("E" & IB) = "¥¼©R¦W"
    
34.     GoTo STAR
    
35.     End If
    
36.          
    
37.     If c Is Nothing Then
    
38.    
    
39.     GoTo AGO
    
40.     Else
    
41.    
    
42. '    IA = IC - 1
    
43. Sheets("»s³y³æ").Range("E" & IB) = Sheets("«È¤á").Range("H" & IC)
    
44.     End If
    
45. STAR:
    
46.     IB = IB + 1
    
47.     IC = 11
    
48.    
    
49.     Loop While Sheets("»s³y³æ").Range("D" & IB) <> ""
    
50.     End Sub

½Æ»s¥N½X

½Ð°Ý¨Ì¤U¦pªG§Úªº«È¤á¤u§@ªí¦b¨ä¥LEXCELÀɸÌ
­n¦p¦ó¥sªü??

toxin

¦^´_ 6# Hsieh

³Â·Ð¤j¤j¤F
Àɮצpªþ¥ó

Hsieh

¦^´_ 5# toxin


    ©Ò¿×¤£¶}±Ò¥u¬Oµøı¤WªºÃö«Y¡A¦pªG­n¨ú±o¬YÀɮפº®e¡A³£¥²¶·¶}±Ò¸ÓÀÉ®×
´Nºâ¬O¸ê®Æ®w¬d¸ß¡A¹ê½è¤WÁÙ¬O¦b­I´º§@·~¤¤¶}±Ò¸ÓÀɮק@¬°¸ê®Æ®w³s½u
§Aªº°ÝÃD³Ì¦n±N2­ÓÀɮצP®Éªþ¤W¡A¤~ª¾¹D°ÝÃD¥X¦b­þ¸Ì

toxin

¦^´_ 4# Hsieh
·PÁ¤j¤j¦^ÂÐ
µ{¦¡©Ò¦b¤u§@ªíªº¬O»s³y³æ¤u§@ªí
¦ý¬O¯à°÷¤£¶}±Ò«È¤á¤u§@ªí¦Óª½±µ¨Ï¥Î¶Ü?
¦]¬°·|¦³¤U¦C°ÝÃD
11¦C¤¤¶}±Ò¤F«È¤á¤u§@ªíªºÀÉ®×
13»s³y³æ¤u§@ªíÀ³¸Ó¤£»Ý­n¥[(WB.)
¦ý¬O11¶}±Ò«È¤áªí«á
¨ì13¦C©Ò¦bªº¤u§@ªí¤£¦P©Ò¥H·|¥X²{"°}¦C¯Á¤Þ¶W¥X½d³ò"

Hsieh

¦^´_ 3# toxin
À³¸Ó¬O§A¨â­ÓÀɮפÀ§O¨Ï¥Î2­ÓExcelÀ³¥Îµ{¦¡¶}±Ò©Ò­P
§Ú·Q§A³o­Óµ{§ÇÀ³¸Ó¬O¦b§t¦³Sheets("»s³y³æ")ªº¬¡­¶Ã¯¤¤¬Y¤@¤u§@ªíªº«ö¶sµ{§Ç
¸Õ¸Õ¥H¤Uµ{¦¡½X

1. Private Sub CommandButton1_Click()
   
2.     Dim a As Integer, i As Integer
   
3.     Dim b As Integer
   
4.     Dim c As Variant
   
5.     Dim d As Variant
   
6.     Dim E As Variant
   
7.     Dim WB As Workbook, IA As Integer, IB As Integer, IC As Integer
   
8.     IA = 7000
   
9.     IB = 3
   
10.     IC = 10
    
11.     Set WB = Workbooks.Open("d:\test.xls")  '¶}±Ò¬¡­¶Ã¯: «È¤á¤u§@ªí©Ò¦bªº¬¡­¶Ã¯
    
12.     Do
    
13.         If WB.Sheets("»s³y³æ").Range("E" & IB) <> "" Then
    
14.             IB = IB + 1
    
15.         End If
    
16.     Loop While Sheets("»s³y³æ").Range("E" & IB) <> ""
    
17. AGO:
    
18.     IC = IC + 1
    
19.     Do
    
20.         d = Sheets("»s³y³æ").Range("D" & IB)
    
21.         Set c = WB.Sheets("«È¤á").Range("B11:B" & IC).Find(What:=d, LookIn:=xlFormulas, _
    
22.          LookAt:=1, SearchOrder:=2, SearchDirection:=xlNext, _
    
23.          MatchCase:=False, MatchByte:=False, SearchFormat:=True)
    
24.         If IC = IA Then
    
25.             Sheets("»s³y³æ").Range("E" & IB) = "¥¼©R¦W"
    
26.             GoTo STAR
    
27.         End If
    
28.         If c Is Nothing Then
    
29.             GoTo AGO
    
30.         Else
    
31.         '    IA = IC - 1
    
32.             Sheets("»s³y³æ").Range("E" & IB) = WB.Sheets("«È¤á").Range("H" & IC)
    
33.         End If
    
34. STAR:
    
35.         IB = IB + 1
    
36.         IC = 11
    
37.     Loop While Sheets("»s³y³æ").Range("D" & IB) <> ""
    
38. WB.Close
    
39. End Sub

½Æ»s¥N½X

toxin

¦^´_ 2# GBKEE
¤£¦n·N«ä
¥i¯à§Úªí¹F¤£¬O«Ü¦n
»s³y³æ¸ò«È¤á©Ò¦bªºÀɮ׬O¤£¤@¼Ëªº
¦pªG³æ¿W¥Î¶}±Òªº¤è¦¡¶}±Ò«È¤á©Ò¦bªºÀÉ®×
µ{¦¡·|¨S¿ìªk¶]¤U¥h
¤£¾å±o¦³¨S¦³¤£¥Î¶}±ÒÀɮתº¤è¦¡ª½±µ½Õ¥Î"«È¤á"¤u§@ªí

GBKEE

¦^´_ 1# toxin
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Private Sub CommandButton1_Click()
   
3.     Dim a As Integer, i As Integer
   
4.     Dim b As Integer
   
5.     Dim c As Variant
   
6.     Dim d As Variant
   
7.     Dim E As Variant
   
8.     Dim WB As Workbook, IA As Integer, IB As Integer, IC As Integer
   
9.     IA = 7000
   
10.     IB = 3
    
11.     IC = 10
    
12.     Set WB = Workbooks.Open("d:\test.xls")  '¶}±Ò¬¡­¶Ã¯: «È¤á¤u§@ªí©Ò¦bªº¬¡­¶Ã¯
    
13.     Do
    
14.         If WB.Sheets("»s³y³æ").Range("E" & IB) <> "" Then
    
15.             IB = IB + 1
    
16.         End If
    
17.     Loop While WB.Sheets("»s³y³æ").Range("E" & IB) <> ""
    
18. AGO:
    
19.     IC = IC + 1
    
20.     Do
    
21.         d = WB.Sheets("»s³y³æ").Range("D" & IB)
    
22.         Set c = WB.Sheets("«È¤á").Range("B11:B" & IC).Find(What:=d, LookIn:=xlFormulas, _
    
23.          LookAt:=1, SearchOrder:=2, SearchDirection:=xlNext, _
    
24.          MatchCase:=False, MatchByte:=False, SearchFormat:=True)
    
25.         If IC = IA Then
    
26.             WB.Sheets("»s³y³æ").Range("E" & IB) = "¥¼©R¦W"
    
27.             GoTo STAR
    
28.         End If
    
29.         If c Is Nothing Then
    
30.             GoTo AGO
    
31.         Else
    
32.         '    IA = IC - 1
    
33.             WB.Sheets("»s³y³æ").Range("E" & IB) = Sheets("«È¤á").Range("H" & IC)
    
34.         End If
    
35. STAR:
    
36.         IB = IB + 1
    
37.         IC = 11
    
38.     Loop While Sheets("»s³y³æ").Range("D" & IB) <> ""
    
39. End Sub

½Æ»s¥N½X