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

[µo°Ý] ½Ð¯q ¬O§_¦³§ó§Öªº¬d¸ß¤è¦¡

[µo°Ý] ½Ð¯q ¬O§_¦³§ó§Öªº¬d¸ß¤è¦¡

¤p§Ì¦³¤@¬O·Q½Ð°Ý
ªþ¥ó½Ð°Ñ¦Ò
°µªk
1.¤À­¶ §Ç¸¹ ¤º®e¦³¨â¸U¦hµ§¸ê®Æ¡C
2.§Ç¸¹¤º®e²Å¦X L ¤À­¶ ©ÎªÌ R¤À­¶ªº °_©lBarcode  ¤Î µ²§ôBarcode °Ï¶¡ ¡C
3.²Å¦X°Ï¶¡ªº§Ç¸¹ ¦b®ÇÃ䪺¤u³æ¸¹½X ¦Û°Ê±a¥X L ©ÎªÌ R °ÏÃ䪺 ¤u³æ¸¹½X¸ê°T¡C
­Ó¤H°µªk¦p¤U
1.¤p§Ì¥ý¥Î¸ê®Æ®w(Arry) ¥ý¥hŪ¨ú¤F L ¸ò Rªº©Ò¦³°_©lBarcode  ¤Î µ²§ôBarcode °Ï¶¡ ¡AÁÙ¦³¤u³æ¸¹½X
2.¦A§Q¥Î°j°é ¤Î IF  ªº¤è¦¡ ¥h§P§O ¬O§_¦b°Ï¶¡¤º¶}±Ò¸òµ²§ôªºBarcode¡C
3.¦pªG²Å¦X±ø¥ó «h±a¥X ¤u³æ¸¹½X¡C
°ÝÃD
ÁöµM L¤À­¶ ¸ò R¤À­¶ªº¸ê®Æ¤£¦h¡A¦ý¬O§Ç¸¹¤À­¶ ¦³¨â¸U¦hµ§¸ê®Æ¡A¥Î°j°é¶]´N·|¶]«Ü¤[(®t¤£¦h20¤ÀÄÁ)
¨C¤@µ§§Ç¸¹ ´N­n¤ñ¹ï L¤À­¶¸ò R¤À­¶ªº¸ê®Æ¡C
·Q½Ð°Ý¤@¤U!!¬O§_«Øij§ó§Ö³tªº¤è¦¡??
ÁÂÁÂ!! ¤u³æ¬d¸ß.zip (266.96 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2021-7-30 01:52 ½s¿è

¦^´_ 1# fantersy

1.¤p§Ì¥ý¥Î¸ê®Æ®w(Arry) ¥ý¥hŪ¨ú¤F L ¸ò Rªº©Ò¦³°_©lBarcode  ¤Î µ²§ôBarcode °Ï¶¡ ¡AÁÙ¦³¤u³æ¸¹½X
2.¦A§Q¥Î°j°é ¤Î IF  ªº¤è¦¡ ¥h§P§O ¬O§_¦b°Ï¶¡¤º¶}±Ò¸òµ²§ôªºBarcode¡C
3.¦pªG²Å¦X±ø¥ó «h±a¥X ¤u³æ¸¹½X¡C
°ÝÃD
ÁöµM L¤À­¶ ¸ò R¤À­¶ªº¸ê®Æ¤£¦h¡A¦ý¬O§Ç¸¹¤À­¶ ¦³¨â¸U¦hµ§¸ê®Æ¡A¥Î°j°é¶]´N·|¶]«Ü¤[(®t¤£¦h20¤ÀÄÁ)


ÅÞ¿è¨S°ÝÃD¡A¤~2¸U¦hµ§¸ê®Æ¡A°µÂ²³æªº§PÂ_¡A²z½×¤W¤£·|¶]³o»ò¤[(20¤ÀÄÁ)

§AÀ³¸Ó¨S¦³¥Î¯Â¼Æ­È¹Bºâ+°}¦C¤@¦¸¿é¥X¡A­Y¬O¤@­Ó­ÓÀx¦s®æ¶]¡A·|«Ü¤[(Àx¦s®æ¬Oª«¥ó)

§Ú¦Û¤v¹Á¸Õ¡A¶]´X¬íÄÁ¦Ó¤w¡A§Ú¹q¸£¤]¬ON¦~«eªº´¶³q¾÷¡A¤£¥i¯à¤ñ§A§Ö³o»ò¦h

§AªºªþÀɨS¦³µ{¦¡(.xlsx)¡AµLªkª¾¹D¬O­þ¸Ìªá¸û¤[®É¶¡
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-30 04:10 ½s¿è

¦^´_ 1# fantersy


    ÁÙ¦³¤@¨âºØ¤è¦¡¤]«Ü§Ö
½Õ¥Îmatch¨ç¼Æ ©ÎªÌvlookup¨ç¼Æ
¥u¤£¹L­n°µ4¦¸

¥»Àɮפº¦³¨âºØ¤è¦¡
¤@¯ë°}¦C³B²z 50¬í
3ºû°}¦C³B²z 1¬í

¤u³æ¬d¸ß v1.zip (902.97 KB)

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-30 04:41 ½s¿è

¦^´_ 3# singo1232001


¥t¥~¸É¥R¤@¤U
3ºû¤è¦¡¤º
Á`¦@¦³3²Õ°}¦C  
ar=©ñ§Ç¸¹¥Î ¥\¯à©â¶H¬° ±íªí¸ê®Æ®w (3ºû°}¦C)
LR=©ñL¸òRªº¤u³æ&°_©lµ²§ô¥Î    ¥\¯à©â¶H¬°  ¬d¸ß¸ê®Æ (2ºû°}¦C)
br=©ñ¤ñ¹ï«áªº¸ê®Æ¦¬¶°ªí¥Î ¥\¯à©â¶H¬° ¶°¤¤¾ã¦X¤ñ¹ï«á¸ê®Æ  (2ºû°}¦C)

¦¹®×»P¤@¯ë¤£¦P¤§³B ¦b©ó
§Ú¬O±N  ¸ê®Æ®w »P¬d¸ß¸ê®Æ ¬O¤Ï¹L¨Ó«Ø¸mªº
ÁöµM©ú­±¤WÁÙ¬O ¥Î¬d¸ß¸ê®Æ ¥h¬d¸ê®Æ®w
¦ý§Ú¤@¶}©l´N§â ¸ê®Æ®w ¸ò ¬d¸ß¸ê®Æ ÄA­Ë¹L¨Ó «Ø¸m³]­pªí

­ì¦]¬O
¥Ñ©óL,R¥»¨­¸ê®Æ¶q¤Ó¤Ö
©Ò¥H¥ÎL,R ¥h¬d§ä¤ñ¹ï§Ç¸¹

¥¿±`¨Ó»¡ À³¸Ó­nL,R¸ê®Æ¯S§O¦h
¬O¥Î§Ç¸¹ ¥h¬d§ä¤ñ¹ï L,R¸ê®Æ®w

¤£¹L¥¿¤Ï¬d§ä¤ñ¹ï ³£¥i¥H°µ
´N¥Ø«e¨Ó»¡ ³t«×ÁÙ¥i¥H

´N­ì²z¨Ó»¡ ±íªí¥D­n©ñ¤j¶q¸ê®Æ ³t«×·|¤ñ¸û§Ö
©Ò¥H¤~§â§Ç¸¹³]¬° ±íªí¸ê®Æ®w
§_«h¥¿±`±¡ªp¤U LR¸ê®Æ¶q¦n´X¸Uµ§
´NÀ³¸Ó­n§âL,R³]¬°±íªí¸ê®Æ®w

¥t¥~¤ºªþ¤T±i¹Ï «K©ó¾Ç²ß»P²z¸Ñ
¤Tºû°}¦C ¥i¥H·Q¹³¦¨ ¶}¤F99±iªº¤u§@ªí ³o¼Ë·|¤ñ¸û®e©ö²z¸Ñ

¦pªG¸ê®Æ¶qÁÙ§ó¤j
«Øijª½±µ¥h¾ÇSQL ¤U»yªk ·|¤ñ¥Îexcel¨Óªº§Ö

¥t¥~¸É¥R
¬°¤°»ò·|¬D²Ä11¦ì »P³Ì«á1¦ì ªº¦r¤¸¨Ó·í°ò·Ç(³o¬O¬°¤F¦¹®×¦ÓÀu¤Æªº³¡¤À)
¦]¬°³o¨â­Ó¦r¤¸ÅܤƸû¬°¥­§¡
©ñ¦b±í¸Ì¤ñ¸û®e©ö¤À´²¶}¨Ó
¤W­zªº¦n³B¬O arªì©l°}¦C³]©w¤j¤p®É ´N¤£¥Î³]¤Ó¤j¤F
¥u­n³]2000¦C ´N¥i¥H©ñ¤J¤F

TOP

¦^´_ 4# singo1232001


    ¥t¥~ ¦³­Ó¤p¥¢»~
Ãö©ó module1¤ºªº sub tt() ¦³¤@¬q §Ú¤Ö¥´¤F ¤@¦æ¥N½X

  For j = 1 To ar(a, 0, 0) - 1
        If TX = ar(a, j, 0) Then
        rw = ar(a, j, 1)  '§Ç¸¹¦C¦ì
        br(rw, 0) = LR(i, 3)   '¤u¤¦¸¹½X
        br(rw, 1) = LR(i, 2)   '¤u§@ªí¦W
        br(rw, 2) = rw         '¦C¦ì
        rw = 0
        Exit For    '<--³o¦æÀ°§Ú¥[¤W¥h
        End If
    Next

³o¼Ë³t«×·|¦b§Ö¤@«}«}

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-7-30 05:16 ½s¿è

¦^´_ 5# singo1232001


    ¦]¬°Àɮפº¦³­Ó¤p¥¢»~ §Ú¤]¶¶«K­×¥¿¤F¤@¤UÀÉ®×
    §Ú§âÀÉ®×­«¶Ç¤@¦¸

3ºû°}¦C¤u³æ¬d¸ß v1.zip (903.4 KB)

TOP

¦^´_ 1# fantersy

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁÂ

Sub test()
Dim Arr, xD, i&, Tm
Tm = Timer
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([L!a1], [L!l65536].End(3))
For i = 2 To UBound(Arr)
    xD(Arr(i, 10)) = Arr(i, 1)
    xD(Arr(i, 12)) = Arr(i, 1)
Next
Arr = Range([R!b1], [R!m65536].End(3))
For i = 2 To UBound(Arr)
    xD(Arr(i, 10)) = Arr(i, 1)
    xD(Arr(i, 12)) = Arr(i, 1)
Next
Arr = Range([§Ç¸¹!a2], [§Ç¸¹!a65536].End(3))
For i = 1 To UBound(Arr)
    Arr(i, 1) = xD(Arr(i, 1))
Next
Sheets("§Ç¸¹").[b2].Resize(UBound(Arr)) = Arr
MsgBox Timer - Tm
End Sub

TOP

°Ï¶¡¤ñ¹ï, ¨S¦³¦n¤èªk, °j°j°é§a!!!
­YBarcode½T©w¬O¥Ñ¤p¦Ó¤j±Æ§Çªº¸Ü, ¥i¥H´î¤Ö§PÂ_´X¦¸~~
Xl0000114.rar (386.29 KB)

TOP

¥»©«³Ì«á¥Ñ samwang ©ó 2021-7-30 12:56 ½s¿è

¦^´_ 1# fantersy


¬Ý¤F­ã¤jªºµª®×¡A¤~ª¾¹D»~·|¼Ó¥Dªº»Ý¨D¤F¡A¥¿½T»Ý¨D­n°Ï¶¡¤ñ¹ï¡A
©Ò¥H½Ð©¿²¤7¼Óµ{¦¡¡AÁÂÁÂ

TOP

¦^´_ 8# ­ã´£³¡ªL


    ·PÁ ­ã¤j ¸Ñ´b
¬Ý¨Ó¯uªº¥u¦³¥Î°j°é¤F

§Ú­ì¥»ªºµ{¦¡§PŪ ¥u­n²Å¦X°Ï¶¡ ¨Ï¥Î
Exit For  ¸õ¥X¤º°j°é
¬Ý¨ì ­ã¤j ªºµ{¦¡ ¤]¬O­Ó¤£¿ùªº¤è¦¡
¦A¦¸·PÁ³á!!

TOP

        ÀR«ä¦Û¦b : ¤Hªº²´·úªø¦b«e­±¡A¥u¬Ý¨ì§O¤Hªº¯ÊÂI¡Aµ·²@¬Ý¤£¨ì¦Û¤vªº¯ÊÂI¡C
ªð¦^¦Cªí ¤W¤@¥DÃD