- ©«¤l
- 200
- ¥DÃD
- 22
- ºëµØ
- 0
- ¿n¤À
- 234
- ÂI¦W
- 34
- §@·~¨t²Î
- Vista
- ³nÅ骩¥»
- Office2003
- ¾\ŪÅv
- 20
- ©Ê§O
- ¨k
- ¨Ó¦Û
- °ª¶¯
- µù¥U®É¶¡
- 2020-4-14
- ³Ì«áµn¿ý
- 2025-2-20
    
|
¦^´_ 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 |
|