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

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

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-8-2 09:01 ½s¿è

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


«O«ù¹êÅç¾Ç²ßºë¯«
¸ò­ã¤j¾Ç¨ì¦n¦h¦r¨åÄ_¶Qªº§Þ¥©

³Ì«á¦^ÀYÄ~Äò´ú¸Õ
­ì¨Ó¥ýÂà°}¦CÁÙ¥i¥H¦b¥[³t

¥Ø«e¤]§â®É¶¡À£ÁY¦¨¥\!
¶¶«K©^¤WÀɮװѦÒ

¥t¥~¤]´ú¸Õ¤F °}¦C»P¦r¨åªº¾A¥Î°Ï¶¡ ¯uªº¦n¦nª±!

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

°}¦C»P¦r¨å´ú³t1.zip (17.35 KB)

TOP

§Ú´ú¸Õ¤F¤@¤U¡A¤j³¡¤ÀL¸òR³£¦³²Å¦XªºBarCode±Ò©l¤Îµ²§ô±ø¥óªº¤u³æ¸¹½X¡A
©Ò¥HL¥ý§ä©ÎR¥ý§ä·|¦³¤£¦Pªºµª®×?

¨Ò¦p
   BarCode              ²Å¦X¦ì¸m
A2 CYR128401TG0WLX2H        R42,L38
A3 CYR128401600WLX2U        R42,L3,L38
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2021-8-3 18:17 ½s¿è

¦^´_ 22# ML089


¦pªG¬O«ü§Úªº³o¥÷Àɮתº¸Ü  "3ºû°}¦C¤u³æ¬d¸ß v4 v1"
°õ¦æ«áªºµ²ªG¦³²§±` ¦³º|¯Ê¸ê®Æ
¬O§Úªºvba¸Ì­±¦³bug

Sub tttt()
t = Timer
Dim L As Worksheet
Dim R As Worksheet
Dim S As Worksheet

Dim i%, j%, w%, rw%, a%
Dim x As String
Dim TX As String


Set L = Sheets("L")
Set R = Sheets("R")
Set S = Sheets("OP")
f = S.Columns("A").Find("*", , -4163, , 1, 2).Row
f0 = L.Columns("A").Find("*", , -4163, , 1, 2).Row
f1 = R.Columns("A").Find("*", , -4163, , 1, 2).Row

ss = S.Range("A2").Resize(f, 1)
SL = L.Range("A2").Resize(f0, 12)
SR = R.Range("B2").Resize(f1, 12)
...

¬õ¦âªº¦a¤è§Ú¥´¿ù¤F À³¸Ó¬OR
ª¬ªp¬O ·|¦³³\¦h¸ê®Æ¨S§ì¨ì
¬Q¤Ñ±ß¤W¤~µo²{ªº
ªþ¤W­×§ï¦nªºÀÉ®×

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

TOP

¦r¨å³o¤@¶ô  §Ú¯uªº«Ü¤£¼ô
³Ìªñ¤~¶}©l±µÄ²
­ã¤j¯uªº¤Ó±j¤F!!¨C¦¸µo°Ý³£¾Ç¨ì¤£¤Ö
·PÁÂÀ°¦£

TOP

¦^´_ 23# singo1232001

»~·|¤F! ¤£¬O»¡§Aµ{¦¡ªº°ÝÃD

§Ú§@¥þ¤ñ¹ïL R¨â­Ó¸ê®Æªí¡A¤j³¡¤À³o¨â²Õ¸ê®Æªí¸Ì³£¦³²Å¦Xªº¦ì¸m¡A
¦]¬°¤j®aªºµ{¦¡³£¬O¥ý¤ñ¬d¸ßLªí¦b¬d¸ßRªí¡A
§Ú¦b½m²ß®É±N¬d¸ß¶¶§Ç§ï¬°¥ý¤ñ¬d¸ßRªí¦b¬d¸ßLªí¡Aµª®×¸ò¤j®a¤£¦P
©Ò¥Hµo²{¸ê®Æ¥h¤ñ¹ï °_©lBarCode ¸ò µ²§ôBarCode¡A¦bL Rªí³£¦³µª®×¡Aº¡©_©Çªº¡C

Sub LR¥þ³¡¤ñ¹ï()
    Dim LR(1 To 2), SN
    TM = Timer
    Set SN = Sheets("§Ç¸¹")
    LR(1) = [L!A1:L1].Resize([L!J65536].End(xlUp).Row)
    LR(2) = [R!B1:M1].Resize([R!J65536].End(xlUp).Row)
    Arr = SN.[A1].Resize(SN.[A65536].End(xlUp).Row, 3)
    ReDim Brr(1 To UBound(Arr), 1 To 4)
    Brr(1, 1) = "¤u³æ¸¹½X": Brr(1, 2) = "LR¦ì¸m": Brr(1, 3) = "°_©lBarCode": Brr(1, 4) = "µ²§ôBarCode"
    For i = 2 To UBound(Arr)
        Key = "": Wno = ""
        For j = 1 To 2
            For k = 2 To UBound(LR(j))
                If LR(j)(k, 10) <= Arr(i, 1) And Arr(i, 1) <= LR(j)(k, 12) Then
                    'Brr(i, 1) = LR(j)(k, 1)
                    'Brr(i, 2) = LR(j)(k, 3) & k
                    'Km = 1: Exit For
                    
                    Brr(i, 1) = Brr(i, 1) & "," & LR(j)(k, 1)      'L R ¤u³æ¸¹½X
                    Brr(i, 2) = Brr(i, 2) & "," & LR(j)(k, 3) & k  'L R ¦ì¸m
                    Brr(i, 3) = Brr(i, 3) & "," & LR(j)(k, 10) & k 'L R °_©lBarcode
                    Brr(i, 4) = Brr(i, 4) & "," & LR(j)(k, 12) & k 'L R µ²§ôBarcode
                End If
            Next
            'If Km = 1 Then Km = 0: Exit For
        Next
    Next
    SN.[D1].Resize(UBound(Arr), 4) = Brr
    SN.Range("D2,E3,F2,G2").Columns.AutoFit
    Debug.Print Timer - TM
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 25# ML089


    ½T¹ê¸ò§AÁ¿ªº¤@¼Ë ¦³­«½Æ

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

TOP

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


    ½Ð±Ð«e½ú ½d¨Ò¤¤ªº§PÂ_¦¡ ¥H¦r¦ê¤ñ¤j¤p! ¬O¥H¤°»ò³W«h¤ñ¤j¤p©Îµ¥©ó?
If Crr(j)(k, 9 + j) <= Arr(i, 1) And Crr(j)(k, 11 + j) >= Arr(i, 1) Then

¦Û¤v¦³´ú¸Õ,¦ý¬OµLªk¤F¸Ñ³W«h!ÄAÂЫá¾Çªº»{ª¾! ¥H¬°¥u¦³¼Æ¦r¤~¯à¤ñ¸û!
Sub TEST_20220719()
If [A2] > [A3] Then
   MsgBox "[A2] > [A3]"
   Else
      MsgBox "[A2] <= [A3]"
End If
End Sub


±zªº½d¨Ò§¹¾ã¤º®e¦p¤U:
Sub TEST_A2()
Dim Arr, Brr, Crr(2), xD(2), T$, i&, j%, k, Km%, TM
TM = Timer
Crr(1) = Range([L!m1], [L!a1].Cells(Rows.Count, 1).End(xlUp))
Crr(2) = Range([R!m1], [R!a1].Cells(Rows.Count, 1).End(xlUp))
For j = 1 To 2
    Set xD(j) = CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(Crr(j))
        T = Left(Crr(j)(i, j + 9), 7)
        If Not IsObject(xD(j)(T)) Then
           Set xD(j)(T) = CreateObject("Scripting.Dictionary")
        End If
        xD(j)(T)(i) = ""
    Next i
Next j
Arr = Range([§Ç¸¹!a1], [§Ç¸¹!a1].Cells(Rows.Count, 1).End(xlUp))
ReDim Brr(1 To UBound(Arr), 1 To 2)
Brr(1, 1) = "¤u³æ¸¹½X": Brr(1, 2) = "¦ì¸m"
For i = 2 To UBound(Arr)
   T$ = Left(Arr(i, 1), 7)
   Km = 0
   For j = 1 To 2
      If Not IsObject(xD(j)(T)) Then
         GoTo 666
      End If
      For Each k In xD(j)(T).keys
         If Crr(j)(k, 9 + j) <= Arr(i, 1) And Crr(j)(k, 11 + j) >= Arr(i, 1) Then
            Brr(i, 1) = Crr(j)(k, j)
            Brr(i, 2) = Mid("LR", j, 1) & k
            Km = 1
            Exit For
         End If
      Next
      If Km = 1 Then
         Exit For
      End If
     
666
   Next j
Next i
[§Ç¸¹!b1].Resize(UBound(Brr), 2) = Brr
MsgBox Timer - TM
End Sub

ÁÂÁ «e½ú«ü¾É!

TOP

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


    «e½ú¤£¦n·N«ä!
±z­ì¥»ªºµ{¦¡½X¤Óºë²¤F!«á¾Ç¾ã²z¦¨¬Ý±oÀ´ªº!
©Ò¥H¼Ó¤W»¡¬O±z­ì¥»ªºµ{¦¡½X!¨ä¹ê¬O«á¾Ç¦³½s¿è¹Lªº!
¹ï¤£°_!

TOP

¦^´_ 27# Andy2483

¦n¤[¨S¤W¨Ó¤F!!!

¤å¦r¤ñ¹ï....¦p¦Pª½±µ±Æ§Ç, ¬O¥i¥Hªº,
¦ý­Y¦³¼Æ¦r¬°¶}ÀY, ¥²¶·"¦r¤¸ªø«×"­n¬Û¦P¤~·|·Ç½T,
¦p: 100A35, 2A46 ¥²¶·¬O 100A35 002A46, 2¤~·|±Æ¦b¤W­±

TOP

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


    ÁÂÁ«e½ú«ü¾É!

TOP

        ÀR«ä¦Û¦b : ¯à·F¤£·F¡A¤£¦p­W·F¹ê·F¡C
ªð¦^¦Cªí ¤W¤@¥DÃD