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

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

¦^´_ 10# samwang

ÁÂÁÂs¤j
§¹¥þ²Å¦X¤u§@»Ý¨D
§Ú¥ý²z¸Ñ¤@¤U
¥i¦³¤@¨Ç¤£À´¦a¤è·Q½Ðs¤j¤j¦AÀ°ª`¸Ñ
·PÁÂs¤j   ÁÂÁÂ
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 10# samwang
S¤j ¤j§A¦n
¦^­ì¾Þ§@¤u§@ªí¤¤.¸g´ú¸Õ«á¥H¥i§¹¥þ¹B§@
¦³­Ó°ÝÃD¬OµLªk§@«OÅ@¤u§@ªí
¥d¦b.Range(.[A4], .Cells(N + 3, 1)).NumberFormatLocal = "@"³o¸Ì
½ÐS¤j¦bÀ°¬Ý¬Ý¦p¦ó¥i¥Î«OÅ@¤u§@ªí¾Þ§@
¦A½ÐS¤j¤j¥i¦bµ{¦¡«á¥[ª`¸Ñ
ÁÂÁ§A
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 10# samwang
S¤j¤j §A¦n
§Ú¤w¦¨¥\§Q¥Î³o2²Õµ{¦¡
Worksheets("¤u§@ªí2").Unprotect ("0123")
Sheets("¤u§@ªí2").Protect ("0123")
¥h°µ«OÅ@°Ê§@
¦ý§Ú«Ü·Qª¾¹Dµ{¦¡µù¸Ñ¤×¨ä¬O³o¬q
.Range(.[A4], .Cells(N + 3, 1)).NumberFormatLocal = "@"
§Ú¦A²z¸Ñ·|¤ñ¸ûºC.¦p¦³§Aµù¸Ñ¥i¥[³t¿Ä³q
¦A¦¸ÁÂÁ s¤j«ü¾É ÁÂÁÂ
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_  samwang
S¤j ¤j§A¦n
¦^­ì¾Þ§@¤u§@ªí¤¤.¸g´ú¸Õ«á¥H¥i§¹¥þ¹B§@
¦³­Ó°ÝÃD¬OµLªk§@«OÅ@¤u§@ªí
¥d¦b. ...
BV7BW µoªí©ó 2021-3-28 23:01


°õ¦æ«e¥i¥H¥ý¸Ñ¶}«OÅ@¡A°õ¦æ§¹²¦«á¥i¦A¥[¤J«OÅ@¡AÁÂÁÂ

TOP

¦^´_ 13# BV7BW


¨º¬qªº·N«ä¬O±N¤u§@ªí2ªºAÄ榳¸ê®Æªº§Ç¸¹§ï¬°¤å¦r®æ¦¡¡AÁÂÁ¡C

TOP

¦^´_ 13# BV7BW


Sub tt1()
Dim Arr, i&, N%, T, pos%, pos2%, pos3%
Sheets("¤u§@ªí2").Range("A4:D1000") = "" '²M°£¤u§@ªí2ªº¸ê®Æ
T = [¤u§@ªí2!C2]   '¬d§ä¦r
Arr = Range([¤u§@ªí3!G1], [¤u§@ªí3!D65536].End(3)) '±N¤u§@ªí3¸ê®ÆD~GÄæ¦ì¸ê®Æ©ñ¦b¼Æ²Õ¤¤
For i = 2 To UBound(Arr)
     pos = InStr(Arr(i, 1), T): pos2 = InStr(Arr(i, 2), T)
     pos3 = InStr(Arr(i, 3), T)        '¬d¸ß¦r½T»{¦³µL¦b¤u§@ªí3ªºD¡BE¡BFÄæ
     If pos > 0 Or pos2 > 0 Or pos3 > 0 Then  '¦³§ä¨ì®É
         N = N + 1: Arr(N, 1) = Format(N, "00")     '¦³¸ê®Æ®ÉArrªº²Ä1Äæ¦ì¡A¦Û°Ê²£¥Í§Ç¸¹
         For j = 2 To 4: Arr(N, j) = Arr(i, j - 1): Next '±N¤u§@ªí3¸ê®ÆD¡BE¡BFÄæ¸ê®Æ¼È®É¦s©ñ¦bArr
     End If
Next
If N > 0 Then '½T»{¦³µL§ä¨ì¸ê®Æ
     With Sheets("¤u§@ªí2")
         .Range(.[A4], .Cells(N + 3, 1)).NumberFormatLocal = "@" 'AÄæ§ï¬°¤å¦r®æ¦¡
         .[A4].Resize(N, 4) = Arr '¦³§ä¨ì¸ê®Æ±Ï¦^¶ñ¦Ü¤u§@ªí2
     End With
End If
End Sub

TOP

¦^´_ 7# BV7BW

­ìµ{¦¡´ú¸Õ®É¨ÃµL·í¾÷°Ý¾÷,©Ò¥H¤£¤F¸Ñ§A©Ò»¡·|·í¾÷¬O¤°»ò°ÝÃD
¥u¬O­ìµ{¦¡¤£ª¾¹D§PÂ_±ø¥ó¬O¥HSheets("¤u§@ªí2").[C2]¬°¥D
­×§ï«áµ{¦¡¦p¤U
  Sub ex()
Dim X$, a As Variant, c As Variant
Set c = Nothing
Sheets("¤u§@ªí2").Range([b4], [b4].End(4).Resize(, 3)).ClearContents
X = Sheets("¤u§@ªí2").[C2]
For Each a In Sheets("¤u§@ªí3").Range([¤u§@ªí3!D2], [¤u§@ªí3!D2].End(4))
   If a = X Or a.Offset(, 1) Like "*" & X & "*" Or a.Offset(, 2) = X Then  '§PÂ_¬O§_¦³²Å¦X±ø¥ó
      If c Is Nothing Then
         Set c = a.Resize(, 3)
      Else
         Set c = Union(c, a.Resize(, 3))
      End If
   End If
Next
c.Copy Sheets("¤u§@ªí2").[b4].Resize(, 3)
End Sub

TOP

¦^´_ 16# samwang
·PÁ S ¤j¤j¥ý¶i ³Ò¤ß«ü¾É ÁÂÁ§A
²{¾ã²Õµ{¦¡¤w§¹¥þ¥i²z¸Ñ¤]¥i¥¿±`¹B§@
¥ý«e¤£¯à«OÅ@°Ê§@.¥H¥[¤WÂê©w§Y¸Ñ°£°Ê§@.¦p¤U
Sub tt1()
Worksheets("¤u§@ªí2").Unprotect ("0123")   '«OÅ@¤u§@ªí2
Dim Arr, i&, N%, T, pos%, pos2%, pos3%
Sheets("¤u§@ªí2").Range("A4:D1000") = ""   '²M°£¤u§@ªí2ªº¸ê®Æ
T = [¤u§@ªí2!C2]     '¬d§ä¦r
Arr = Range([¤u§@ªí3!G1], [¤u§@ªí3!D65536].End(3))   '±N¤u§@ªí3¸ê®ÆD~GÄæ¦ì¸ê®Æ©ñ¦b¼Æ²Õ¤¤
For i = 2 To UBound(Arr)
     pos = InStr(Arr(i, 1), T): pos2 = InStr(Arr(i, 2), T)
     pos3 = InStr(Arr(i, 3), T)        '¬d¸ß¦r½T»{¦³µL¦b¤u§@ªí3ªºD¡BE¡BFÄæ
     If pos > 0 Or pos2 > 0 Or pos3 > 0 Then    '¦³§ä¨ì®É
         N = N + 1: Arr(N, 1) = Format(N, "00")     '¦³¸ê®Æ®ÉArrªº²Ä1Äæ¦ì¡A¦Û°Ê²£¥Í§Ç¸¹
         For j = 2 To 4: Arr(N, j) = Arr(i, j - 1): Next   '±N¤u§@ªí3¸ê®ÆD¡BE¡BFÄæ¸ê®Æ¼È®É¦s©ñ¦bArr
     End If
Next
If N > 0 Then   '½T»{¦³µL§ä¨ì¸ê®Æ
     With Sheets("¤u§@ªí2")
         .Range(.[A4], .Cells(N + 3, 1)).NumberFormatLocal = "@"   'AÄæ§ï¬°¤å¦r®æ¦¡
         .[A4].Resize(N, 4) = Arr   '¦³§ä¨ì¸ê®Æ±Ï¦^¶ñ¦Ü¤u§@ªí2
     End With
End If
Sheets("¤u§@ªí2").Protect ("0123")   '¨ú®ø«OÅ@¤u§@ªí2
End Sub
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 17# jcchiang
ÁÂÁÂJ¤j¤j¦^À³ÁÂÁÂ
²{§Ú¦A´ú¸Õ¤@¤U
²Ä¤@ª©¥»¬O«ö¤U«á¾ãµe­±¨S¦³¦^À³=·í¾÷
²{§Ú¦A¥H²Ä2ª©¥»´ú¸Õ
¦A¦V§A³ø§i´ú¸Õµ²ªG
¦A¦¸ÁÂÁ§A  J¤j¤j ÁÂÁÂ
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

¦^´_ 19# BV7BW
J¤j¤j §A¦n
²Ä2ª©¥»¸g´ú¸Õ«á
1)¥i¥¿±`¹B§@©Ò»Ý¬d¸ß°Ê§@
¦³1ÂI¬OµLªk¦A¥Î"B"Äæ¬d¸ß
¥i»¡¥ÎA101´N¥i¬d¸ß.¦p¥ÎA´N¤£¥i¦æ.¥X²{¿ù»~*c.Copy Sheets("¤u§@ªí2").[b4].Resize(, 3)*
¦b"C"."D"Ä椤´N¥i¥H.¦p"D"Äæ¿é¤J"°ª"´N¥i¬d¸ß¹B§@
2)·í§ÚÂà´«¦Ü¹ê»Ú¨Ï¥Î¤u§@ªí®É
·|¥X²{¿ù»~*Sheets("¤u§@ªí2").Range([b4], [b4].End(4).Resize(, 3)).ClearContents*
©¹¤U´NµLªk¦A´ú¸Õ
¦A½Ð°Ý¤u§@ªí3¤¤"A""B""C"Äæ¦bµ{¦¡¤¤¦³¥Î³~.©Î¬O¦³»Ý­n?
¦b³o¸Ì¥ý¦VJ¤j¤j »¡ «D±`·PÁÂ
Åý§Ú¦A¦h¤@¼h²`¤J
¥i½ÐJ¤j¤j¦AÀ°µ{¦¡ª`¸Ñ
«D±`ÁÂÁ§A
ªþ¤W´ú¸ÕÀÉ
ÃöÁä¦rvba­×§ï - ½Æ»s.zip (130.91 KB)
±Ó¦Ó¦n¾Ç,¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD