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

[µo°Ý] ¨Ì«ü©w°Ï¶¡¤é´Á¡B±b¸¹ ¶ñ¤J¸ê®Æ

¦^´_ 10# PJChen

§â°}¦C§ï¦¨ CELLS À³¸Ó¥i¥H ¦³ªÅ¦A¸Õ¸Õ¬Ý¦æ¤£¦æ ·PÁÂ
  1. Sub ¹w¬ù§ó·s_2()
  2. Application.ScreenUpdating = False
  3.     Arr = [»¡©ú!I2].CurrentRegion
  4.     Brr = [¹w¬ù!A1].CurrentRegion

  5.     For X = 2 To UBound(Arr)
  6.         A = Arr(X, 1) & "-" & Arr(X, 4)
  7.         For Y = X To UBound(Brr)
  8.             B = Brr(Y, 2) & "-" & Brr(Y, 1)
  9.             If A = B And Brr(Y, 3) = "" Then K = 0
  10.             If A = B And Arr(X, 4) <> "" And K <> 1 Then
  11.                 K = 1
  12.                 Cells(Y, 3) = Arr(X, 7)
  13.                 Cells(Y, 7) = "#0000" & Arr(X, 5)
  14.             Exit For
  15.             End If
  16.         Next Y
  17.     Next X

  18. Application.ScreenUpdating = True
  19. End Sub
½Æ»s¥N½X

TOP

¦^´_ 11# °a¤ªºµ

¯u·PÁ¡I°õ¦æ¨S°ÝÃD¤F
½Ð°Ý­ì¨Óªºµ{¦¡,¤£»Ý­n¤F¶Ü? [¹w¬ù!A1].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr
2 To UBound(Arr)...UBound(Arr)«e­±ªº¼Æ¦r¥Nªí¤°»ò?§Ú±`¬Ý¨ìµ{¦¡«e¦³¤£¦P¼Æ¦r,¦ý¤£²z¸Ñ¥Îªk¡I
¥i¥Hªº¸Ü¯à§_À°¦£¸Ñ»¡±z¼gªºµ{¦¡¡H

TOP

¦^´_ 12# PJChen

Sub ¹w¬ù§ó·s_2()
Application.ScreenUpdating = False 'Ãö³¬§ó·sµe­±

    '§â¤u§@ªíªº©Ò¦³¤º®e©ñ¨ì°}¦C
    Arr = [»¡©ú!I2].CurrentRegion
    Brr = [¹w¬ù!A1].CurrentRegion

    For X = 2 To UBound(Arr) '¦b°}¦C¸Ìªº²Ä2¦C¶}©l¨ì³Ì«á¤@¦C
        A = Arr(X, 1) & "-" & Arr(X, 4) '»¡©ú¤å¦r ±b¸¹¦êÁp¤é´Á
        For Y = X To UBound(Brr) '¦b°}¦C¸Ìªº²Ä2¦C¶}©l¨ì³Ì«á¤@¦C
            B = Brr(Y, 2) & "-" & Brr(Y, 1) '¹w¬ù¤å¦r ±b¸¹¦êÁp¤é´Á
            If A = B And Brr(Y, 3) = "" Then K = 0 '¦pªG»¡©ú¤å¦r»P¹w¬ù¤å¦r¬Û¦P ¥B¹w¬ùX¦C¦ìªº²Ä3Äæ¦ì¨S¦³¤º®e K´Nµ¥©ó0 ªí¥Ü¨S¦³³Q¤ñ¹ï¹L
            If A = B And Arr(X, 4) <> "" And K <> 1 Then '¦pªG»¡©ú¤å¦rªº»P¹w¬ù¤å¦r¬Û¦P ¥B»¡©úX¦C¦ìªº²Ä4Äæ¦ì¦³¤º®e ¥BK¤£µ¥©ó1 ªí¥Ü¨S¦³³Q¤ñ¹ï¹L
                K = 1 'ªí¥Ü¤w¸g³Q¤ñ¹ï¹L
                Cells(Y, 3) = Arr(X, 7) '§â»¡©úªº¹w¬ùªO¼Æ©ñ¨ì¹w¬ù¤u§@ªíªº³æ¶µ¨úªOÄæ¦ì
                Cells(Y, 7) = "#0000" & Arr(X, 5) '§â»¡©úªº "#0000"¦êÁp¨úªO½s¸¹ ©ñ¨ì¹w¬ù¤u§@ªíªº¨úªO½s¸¹Äæ¦ì
            Exit For '¸õ¨ì¤U¤@­Ó°j°é
            End If
        Next Y
    Next X
    'D:FÄ檺¤½¦¡­È¤Æ ¬O¦]¬°  Brr = [¹w¬ù!A1].CurrentRegion ªºÃö«Y ©Ò¦³¤º®e©ñ¨ì°}¦C ©Î§ï¦¨¯S©w¦ì¸m
'[¹w¬ù!A1].Resize(UBound(Brr, 1), UBound(Brr, 2)) = Brr ¦]¬°¨S¦³¦b°}¦C¸Ì¤ñ¹ï©Ò¥H¤£»Ý­n
Brr«üªº¬O¹w¬ù¤u§@ªí¤ñ¹ï«áªº¤º®e
¨ä¹ê¥Î¦r¨å¤èªk·|§ó¦n ¦ý¤p§ÌÁÙ¦b¾Ç²ß¤¤¤£¤Ó²z¸Ñ...¦³ªÅÁÙ¬O·|¨Ó¬Ý¬Ý¤j¤j­Ì«ç»ò¼g.¶¶«K¾Ç²ß^^"
Application.ScreenUpdating = True '¶}±Ò§ó·sµe­±
End Sub

TOP

¦^´_ 13# °a¤ªºµ

½Ð°Ý§Ú¬d¤FCurrentRegion¬O·í«e°Ï°ìªº·N«ä,
¨ºArr = [»¡©ú!I2].CurrentRegion
¬O«ü±qI2¨ì­þ­ÓÀx¦s®æ¬°¤î?

TOP

¦^´_ 14# PJChen

¨º¬O¤@­Ó°ÊºA½d³ò ·|ÀHµÛÀx¦s®æ¤º®eÅÜ°Ê ¦ý«e´£¬O ¥u­n¦³¤º®eªºÀx¦s®æ ³£¬O§A­nªº §_«h·|¿ï¨ì¤@¨Ç¤£¬ÛÃöªº¤º®e
¨ä¹ê«Ü¦h°ÝÃD ³£¥i¥H¦b³o¸Ì§ä¨ìµª®×

¹F¤HÁ`µ²ªºVBAºë¨åªº""¤K""Ãþ¦Ê±ø»y¥yª©

·s¤â VBA ¤Jªù±`¨£°ÝÃD

§Ú³£¬O¦b³o¸Ì¾Çªº ¦pªG§ä¤£¨ìµª®× ¬Ý¬Ý¤@¨Ç¤å³¹¸Ì¤]³\·|¦³  ¦A¤£¦æ«e½ú­Ì¤]·|¦³µª®× ³o¸Ì¯uªº¥i¥H¾Ç¨ì«Ü¦h
¦]¬°¨C­Ó«e½úªº«ä¦ÒÅÞ¿è³£¦³©Ò¤£¦P ¦³®É­Ô¬Ý¨ì«e½ú­Ì¼g ·|µo²{ «z~­ì¨ÓÁÙ¥i¥H³o¼Ë......  ¨þ¨þ..

TOP

¦^´_ 15# °a¤ªºµ


ÁÂÁ±z, ¦³°ÝÃD·|¦A½Ð±Ð

TOP

¦^´_ 15# °a¤ªºµ

ºµ¤j¦n,

¤µ¤Ñ°µ¤À§å¹w¬ù®É,µo¥Í¤F°ÝÃD,½ÐÀ°¦£¬d¬Ýµ{¦¡,·PÁ¡I

»¡©ú¤u§@ªíªºÄx¦â°Ï¶ô¥ý¶i¦æ²Ä¤@¦¸¹w¬ù,¶ñ¤J¸ê®Æ¨S°ÝÃD
¶À¦â°Ï¶ô¬O²Ä¤G§å¹w¬ù¸ê®Æ,¶K¨ìL11:S19«á,µ{¦¡´NµLªk¸ü¤J¸ê®Æ¦Ü"¹w¬ù¤u§@ªí",
¸g¹L´ú¸Õ,¥u­n§â"¹w¬ù¤u§@ªí"ªº«ü©w°Ï¶¡¸ê®Æ²M°£,¶ñ¤J¸ê®Æ´N¥i¥¿±`°õ¦æ,
½Ð°Ý¦³¨S¦³¿ìªk,·í¸ê®Æ¤À§å¥X²{®É,¤£¥Î°µ²M°£°Ê§@,´N¥i¥H¸ü¤J¸ê®Æ??
¹w¬ù¨úªO.rar (37 KB)

TOP

¦^´_ 17# PJChen

§Ú¬Ý¤£¥X¨Ó¡D¡D¡D¡@¡ÖÈСա@¡@¥i¤£¥i¥H½Ð¡@PJChen¤j¤j¡@°µ°O¸¹©ÎµÛµe®Ø®Ø¡@»¡²M·¡¤@ÂI¡@©êºp¡@§Ú²z¸Ñ¯à¤O¤£¤Ó¦n¡@³Â·Ð©p¤F¡@·PÁÂ

TOP

¦^´_ 17# PJChen

¦n¹³¬O¡@¢ÚÄæ®æ¦¡¡@¤£¬O¤é´Á®æ¦¡¡@¦³§ï¤F¤@¤U¡@¦³ªÅ©p¦A¸Õ¸Õ¬Ý¬O¤£¬O¢ÚÄ檺°ÝÃD¡@·PÁÂ

¹w¬ù¨úªO0305.rar (37.36 KB)

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2021-3-5 04:53 ½s¿è

¦^´_ 17# PJChen

©êºp¡@­è¤~µo²{¡@­nÂI¦n´X¦¸¸ê®Æ¤~·|¥þ³¡¦C¥X¡@§PÂ_¨S§ï¦n¡@¦A¶Ç¤@¦¸¡@¡ÖÈСա@¦³ªÅ¦AÀ°§Ú¬Ý¬ÝÁÙ¦³¨S¦³¨ä¥¦°ÝÃD¡@·PÁÂ

¹w¬ù¨úªO030501.rar (37.36 KB)

TOP

        ÀR«ä¦Û¦b : ¦a¤WºØ¤Fµæ¡A´N¤£©öªø¯ó¡F¤ß¤¤¦³µ½¡A´N¤£©ö¥Í´c¡C
ªð¦^¦Cªí ¤W¤@¥DÃD