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

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

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

  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.    Do
  13.       If Sheets("»s³y³æ").Range("E" & IB) <> "" Then
  14.       IB = IB + 1
  15.       End If
  16.       Loop While Sheets("»s³y³æ").Range("E" & IB) <> ""

  17.       
  18.    
  19. AGO:


  20. IC = IC + 1
  21. Do

  22.    
  23.     d = Sheets("»s³y³æ").Range("D" & IB)
  24.     Set c = Sheets("«È¤á").Range("B11:B" & IC).Find(What:=d, LookIn:=xlFormulas, _
  25.          LookAt:=1, SearchOrder:=2, SearchDirection:=xlNext, _
  26.          MatchCase:=False, MatchByte:=False, SearchFormat:=True)
  27.     If IC = IA Then
  28.     Sheets("»s³y³æ").Range("E" & IB) = "¥¼©R¦W"
  29.     GoTo STAR
  30.     End If
  31.          
  32.     If c Is Nothing Then
  33.    
  34.     GoTo AGO
  35.     Else
  36.    
  37. '    IA = IC - 1
  38. Sheets("»s³y³æ").Range("E" & IB) = Sheets("«È¤á").Range("H" & IC)
  39.     End If
  40. STAR:
  41.     IB = IB + 1
  42.     IC = 11
  43.    
  44.     Loop While Sheets("»s³y³æ").Range("D" & IB) <> ""
  45.     End Sub
½Æ»s¥N½X
½Ð°Ý¨Ì¤U¦pªG§Úªº«È¤á¤u§@ªí¦b¨ä¥LEXCELÀɸÌ
­n¦p¦ó¥sªü??

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

TOP

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

TOP

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

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

TOP

¦^´_ 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­þ¸Ì
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 6# Hsieh

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

0508.rar (71.58 KB)

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD