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

[µo°Ý] ÃöÁä¦r¬d¸ß¥i§ï¬°VBA¤è¦¡«ö¶s¬d¸ß

[µo°Ý] ÃöÁä¦r¬d¸ß¥i§ï¬°VBA¤è¦¡«ö¶s¬d¸ß

¦U¦ì·s¶i.¦Ñ®v.¤j®a¦n
§Ú¦bºô¸ô¤W¾Ç²ßÃöÁä¦r¬d¸ß¥Îªk
¨ä¯ÊÂI¬O¤½¦¡»rÅS.®e©ö»~IJ®ø°£.
¬O§_¥i§ï¥ÎVBA¼gªk
«ö¶s¦¡¬d¸ß
ÃöÁä¦rvba.zip (17.26 KB)
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

¦^´_ 1# BV7BW

¸Õ¸Õ¬Ý
  Sub ex()
Dim arr As Variant, a As Object, X%
Set arr = Sheets("¤u§@ªí3").Range(Sheets("¤u§@ªí3").[F2], Sheets("¤u§@ªí3").[d2].End(4))
With Sheets("¤u§@ªí2")
   For Each a In .Range(.[a4], .[a4].End(4))
      For X = 1 To arr.Rows.Count
         If a = Int(Replace(arr(X, 1), "A", "") - 100) Then
            a.Offset(, 1).Resize(, 3) = Application.Transpose(Application.Transpose(arr(X, 1).Resize(, 3)))
            Exit For
         End If
      Next
   Next
End With
End Sub

TOP

¦^´_ 1# BV7BW

½Ð´ú¸Õ¬Ý¬Ý¡AÁÂÁ¡C
Sub tt()
Dim Arr, xD, i&, N%, T
Set xD = CreateObject("Scripting.Dictionary")
Sheets("¤u§@ªí2").Range("b4:d200") = ""
Arr = Range([¤u§@ªí3!F1], [¤u§@ªí3!D65536].End(3))
For i = 2 To UBound(Arr)
    T = Int(Replace(Arr(i, 1), "A", "") - 100)
    xD(T & "") = Array(Arr(i, 1), Arr(i, 2), Arr(i, 3))
Next
Arr = Range([¤u§@ªí2!D3], [¤u§@ªí2!A65536].End(3))
For i = 2 To UBound(Arr)
    If xD.Exists(Arr(i, 1) & "") Then
        Arr(i, 2) = xD(Arr(i, 1) & "")(0)
        Arr(i, 3) = xD(Arr(i, 1) & "")(1)
        Arr(i, 4) = xD(Arr(i, 1) & "")(2)
        N = N + 1
    End If
Next
If N > 0 Then Sheets("¤u§@ªí2").[A3].Resize(N, 4) = Arr
End Sub

TOP

ÁÂÁÂ.J¤j.S¤j2¦ì«üÂI
¹ý¸Õ«á¦A¸ò2¦ì³ø§i
ÁÂÁ§A­Ì
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 2# jcchiang
S¤j¤j §A¦n
·PÁ§A©Ò´£¨Ñµ{¦¡.¸gºt½m.·|·í¾÷
¬GµLªk¦V§A´£¥X³ø§i
¥i¦]§Ú²Ó»¡¤£¥þ
¦A«×¦V§A´£¥X»¡©ú¦p¤U

§Ç¸¹=IF(B4="","",TEXT(ROW()-3,"00"))
(§Ç¸¹)¥u¬O¼Æ¦r¤£¯S©w·N¸q.¬OÅý¨Ï¥Î¤Hª¾¹D¦³´X­Ó¬Û¦P¶µ½s.¶µ¥Ø.¦ì¸m.©Ò¥H¥iµL¥i¦³.¤]¥i¬Ù¦C
¦]©Ò»Ý¬d¸ßÁc¦h©Î¬O³æ¤@¼Æ.µ¥¬d¸ß«á¥X²{¸ê®Æ¦A¥[¥H§Ç¸¹.¤£¯à¥þ¥ý¶ñ¤W¼Æ¦r.©Ò¥»¨Ò¬O¦]«e­zµ{¦¡©Ò»Ý¥ý¦æ¿é¤J100®æ

¶µ½s(B3).¶µ¥Ø(C3).¦ì¸m(D3).«h¥HC2®æ¬°¬d¸ß¨Ó·½ÃöÁä¦r
·í²Å¦X¤u§@ªí"C2"¤ºÃöÁä¦r.«h¦b¤u§@ªí"B4"."C4"."D4".¤¤§e²{.¦h¼ÆÃöÁä¦r®É.«h©¹¤U±Æ§Ç

1)¦p¤u§@ªí2"C2"¥´¤W"§Ú"«h¦b¤u§@ªí3"¤¤"D"."E"."F"§@¬d¸ß¤ñ¹ï.²Å¦X¦³"§Ú"¦r«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D"§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"§Ú".¬O¦b¤u§@ªí3"E"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"C4"Äæ
¥t¤u§@ªí2"B4""D4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"D"."F"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"B4""D4"§e¦C

2)¬Û¦P¦p¦b¤u§@ªí2"C2"¥´¤W"A101".«h¦b¤u§@ªí3¤¤"D"."E"."F"§@¬d¸ß²Å¦X¦³A101.«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D".§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"A101".¬O¦b¤u§@ªí3"D"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"B4"Äæ
¥t¤u§@ªí2"C4""D4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"E"."F"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"C4""D4"§e¦C

3)¦p¦P¦b¤u§@ªí2"C2"¥´¤W"¥Ò".«h¦b¤u§@ªí3¤¤"D"."E"."F"§@¬d¸ß²Å¦X¦³"¥Ò".«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D".§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"¥Ò".¬O¦b¤u§@ªí3"F"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"D4"Äæ
¥t¤u§@ªí2"B4""C4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"D"."E"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"B4""C4"§e¦C

¦P¼Ë°²¦p¤u§@ªí2"B4"¸g¹ï·Ó¤ñ¹ï«á¤DµMªÅ¦r.«h´N¬OªÅ¦r."C"."D"¤]¬O¦P¼Ë

*ÃöÁä¬O¦b¤u§@ªí2"C2"¥h¹ï·Ó¤ñ¹ï.¤u§@ªí3"D"."E"."F"Äæ.«á¦A¦^¶Ç¨ì¤u§@ªí2"B4"."C4"."D4".¨Ã©¹¤U±Æ§Ç*
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 3# samwang
s¤j¤j §A¦n
·PÁ§A©Ò´£¨Ñµ{¦¡.¦V§A³ø§i¹Lµ{
¸gºt½m«á.¥u¯à¬d¸ß1¦¸¥B¦^¶Ç·|¦³¶¡¹j
¨Ò¦p¦b¤u§@ªí"C2"¤¤¿é¤J"§Ú"
©Ò§e²{¬O§â¤u§@ªí3§¹¥þÂлs¨ì¤u§@ªí2¤¤

¥i¦]§Ú²Ó»¡¤£¥þ
¦A«×¦V§A´£¥X»¡©ú¦p¤U

§Ç¸¹=IF(B4="","",TEXT(ROW()-3,"00"))
(§Ç¸¹)¥u¬O¼Æ¦r¤£¯S©w·N¸q.¬OÅý¨Ï¥Î¤Hª¾¹D¦³´X­Ó¬Û¦P¶µ½s.¶µ¥Ø.¦ì¸m.©Ò¥H¥iµL¥i¦³.¤]¥i¬Ù¦C
¦]©Ò»Ý¬d¸ßÁc¦h©Î¬O³æ¤@¼Æ.µ¥¬d¸ß«á¥X²{¸ê®Æ¦A¥[¥H§Ç¸¹.¤£¯à¥þ¥ý¶ñ¤W¼Æ¦r.©Ò¥»¨Ò¬O¦]«e­zµ{¦¡©Ò»Ý¥ý¦æ¿é¤J100®æ

¶µ½s(B3).¶µ¥Ø(C3).¦ì¸m(D3).«h¥HC2®æ¬°¬d¸ß¨Ó·½ÃöÁä¦r
·í²Å¦X¤u§@ªí"C2"¤ºÃöÁä¦r.«h¦b¤u§@ªí"B4"."C4"."D4".¤¤§e²{.¦h¼ÆÃöÁä¦r®É.«h©¹¤U±Æ§Ç

1)¦p¤u§@ªí2"C2"¥´¤W"§Ú"«h¦b¤u§@ªí3"¤¤"D"."E"."F"§@¬d¸ß¤ñ¹ï.²Å¦X¦³"§Ú"¦r«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D"§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"§Ú".¬O¦b¤u§@ªí3"E"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"C4"Äæ
¥t¤u§@ªí2"B4""D4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"D"."F"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"B4""D4"§e¦C

2)¬Û¦P¦p¦b¤u§@ªí2"C2"¥´¤W"A101".«h¦b¤u§@ªí3¤¤"D"."E"."F"§@¬d¸ß²Å¦X¦³A101.«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D".§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"A101".¬O¦b¤u§@ªí3"D"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"B4"Äæ
¥t¤u§@ªí2"C4""D4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"E"."F"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"C4""D4"§e¦C

3)¦p¦P¦b¤u§@ªí2"C2"¥´¤W"¥Ò".«h¦b¤u§@ªí3¤¤"D"."E"."F"§@¬d¸ß²Å¦X¦³"¥Ò".«h¦^¶Ç¦Ü¤u§@ªí2¤¤"B"."C"."D".§e¦C§¹¦¨
¦]¦b¤u§@ªí3¤¤©Ò¬d¸ß"¥Ò".¬O¦b¤u§@ªí3"F"Äæ§ä¥X.©Ò¥H¦^¶Ç¦Ü¤u§@ªí2"D4"Äæ
¥t¤u§@ªí2"B4""C4"«h¥H¤u§@ªí3¤¤©Ò¬d¸ß¦C¤¤"D"."E"§@§¹¥þ¹ï·Ó¤ñ¹ï¦^¶Ç¦Ü¤u§@ªí2"B4""C4"§e¦C

¦P¼Ë°²¦p¤u§@ªí2"B4"¸g¹ï·Ó¤ñ¹ï«á¤DµMªÅ¦r.«h´N¬OªÅ¦r."C"."D"¤]¬O¦P¼Ë

*ÃöÁä¬O¦b¤u§@ªí2"C2"¥h¹ï·Ó¤ñ¹ï.¤u§@ªí3"D"."E"."F"Äæ.«á¦A¦^¶Ç¨ì¤u§@ªí2"B4"."C4"."D4".¨Ã©¹¤U±Æ§Ç*
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 2# jcchiang
©êºp
J¤j¤j
§Ú§â§A¦WºÙ»¡¿ù.½Ð§A­ì½Ì
·PÁ§A©Ò´£¨Ñµ{¦¡.¸gºt½m·|·í¾÷
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 3# samwang
­è§Ñ¤Ö¶ÇÀÉ®×
²{¦b¸É¤W
ÃöÁä¦rvba­×§ï.zip (26.81 KB)
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ BV7BW ©ó 2021-3-27 16:23 ½s¿è

¦^´_ 3# samwang
­è§Ñ¤Ö¶ÇÀÉ®×
²{¦b¸É¤W
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 8# BV7BW


½Ð¦A´ú¸Õ¬Ý¬Ý¡A·PÁ¡C
Sub tt1()
Dim Arr, i&, N%, T, pos%, pos2%, pos3%
Sheets("¤u§@ªí2").Range("A4:D1000") = ""
T = [¤u§@ªí2!C2]
Arr = Range([¤u§@ªí3!G1], [¤u§@ªí3!D65536].End(3))
For i = 2 To UBound(Arr)
    pos = InStr(Arr(i, 1), T): pos2 = InStr(Arr(i, 2), T)
    pos3 = InStr(Arr(i, 3), T)
    If pos > 0 Or pos2 > 0 Or pos3 > 0 Then
        N = N + 1: Arr(N, 1) = Format(N, "00")
        For j = 2 To 4: Arr(N, j) = Arr(i, j - 1): Next
    End If
Next
If N > 0 Then
    With Sheets("¤u§@ªí2")
        .Range(.[A4], .Cells(N + 3, 1)).NumberFormatLocal = "@"
        .[A4].Resize(N, 4) = Arr
    End With
End If
End Sub

TOP

        ÀR«ä¦Û¦b : °µ¦n¨Æ¤£¯à¤Ö§Ú¤@¤H¡A°µÃa¨Æ¤£¯à¦h§Ú¤@¤H¡C
ªð¦^¦Cªí ¤W¤@¥DÃD