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

[µo°Ý] ­«½Æ¸ê®Æ¡A¦p¦ó¨Ï¥ÎVBA¤è¦¡¡A«ü©w¯S©w¦r¦ê»s§@¦¨²M³æ(¤w¸Ñ¨M)

[µo°Ý] ­«½Æ¸ê®Æ¡A¦p¦ó¨Ï¥ÎVBA¤è¦¡¡A«ü©w¯S©w¦r¦ê»s§@¦¨²M³æ(¤w¸Ñ¨M)

¥»©«³Ì«á¥Ñ totes ©ó 2011-12-18 20:56 ½s¿è

»Ý¨D¬°¨Ï¥ÎVBA¤è¦¡¡A±NDPARTNOÄæ¦ì¡A«ü©wTC¦r¦ê¶}ÀY¶µ¥Ø¡A»s§@¦¨²M³æ¤è¦¡
1.µ²ªG¦pG6~G9½d¨Ò (¥u»ÝTC¶}ÀY¶µ¥Ø)
2.³Ì²×»Ý¨D¦pG11¦Û§@¦¨¤U©Ô¿ï³æ

¦]¥t¦³¨ä¥L¥Î³~¡A°£¼Ï¯Ã¤ÀªR¥H¥~¡A¥H¤W±Ô­z¦³¨S¦³¿ìªk¥ÎVBA¤è¦¡ªí¹F(²Ä1ÂI§Y¥i)
·PÁ¤F
­«½Æ¸ê®Æ¡A¦p¦ó¨Ï¥ÎVBA¤è¦¡¡A«ü©w¯S©w¦r¦ê»s§@¦¨²M³æ.rar (62.08 KB)

¦^´_ 1# totes
   ªì¾ÇªÌVBA
  1. Sub xx()

  2.     Range("G:G") = ""
  3.     I = 2
  4.     X = 1
  5.     Do While Cells(I, 5) <> ""
  6.        If (Cells(I, 5) Like "TC*") And (Range("G:G").Find(WHAT:=Cells(I, 5)) Is Nothing) Then
  7.           Cells(X, 7) = Cells(I, 5)
  8.           X = X + 1
  9.        End If
  10.        I = I + 1
  11.     Loop
  12.     End Sub
½Æ»s¥N½X
­«½Æ¸ê®Æ¡A¦p¦ó¨Ï¥ÎVBA¤è¦¡¡A«ü©w¯S©w¦r¦ê»s§@¦¨²M³æ.zip (92.93 KB)

TOP

¥i¥H¨Ï¥Î¡A¯uªº·PÁ¸U¤À^ ^

TOP

¦^´_ 1# totes
  1. Option Explicit
  2. Sub Ex()
  3.     Dim S As String, I As Integer, W As String
  4.     S = ""
  5.     I = 2
  6.     [G:G] = ""
  7.     Do While Cells(I, 5) <> ""
  8.         W = Trim(Cells(I, 5))
  9.        If W Like "TC*" And InStr(S, W & ",") = 0 Then
  10.           S = S & W & ","
  11.        End If
  12.        I = I + 1
  13.     Loop
  14.     If S <> "" Then
  15.         S = Mid(S, 1, Len(S) - 1)
  16.         '1.µ²ªG¦pG6~G9½d¨Ò (¥u»ÝTC¶}ÀY¶µ¥Ø)
  17.         [G1].Resize(UBound(Split(S, ",")) + 1) = Application.Transpose(Split(S, ","))
  18.         
  19.         '2.³Ì²×»Ý¨D¦pG11¦Û§@¦¨¤U©Ô¿ï³æ
  20.         With [G11].Validation
  21.             .Delete
  22.             .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  23.             xlBetween, Formula1:=S
  24.         End With
  25.     End If
  26. End Sub
½Æ»s¥N½X

TOP

·PÁª©¥D¡A³s¤U©Ô¿ï³æ³£¦³¤F¡A¼F®`!

TOP

¦^´_ 5# totes
¤£¶·¨Ï¥Î½d³ò§@²M³æ
  1. Sub ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. C = InputBox("¿é¤J¶}ÀY¦r¤¸", , "TC")
  4. ar = Range("E2", [E65536].End(xlUp)).Value
  5. For Each a In ar
  6.   If a Like C & "*" Then d(a) = ""  '¤£­«½Æ²Å¦X³W«h
  7. Next
  8. If d.Count = 0 Then MsgBox "µL²Å¦X¸ê®Æ": Exit Sub
  9. With Range("G11").Validation
  10. .Delete
  11. .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
  12.         xlBetween, Formula1:=Join(d.keys, ",")
  13.         .IgnoreBlank = True
  14.         .InCellDropdown = True
  15.         .InputTitle = ""
  16.         .ErrorTitle = ""
  17.         .InputMessage = ""
  18.         .ErrorMessage = ""
  19.         .IMEMode = xlIMEModeNoControl
  20.         .ShowInput = True
  21.         .ShowError = True
  22. End With
  23. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD