標題:
[發問]
重複資料,如何使用VBA方式,指定特定字串製作成清單(已解決)
[打印本頁]
作者:
totes
時間:
2011-12-18 12:13
標題:
重複資料,如何使用VBA方式,指定特定字串製作成清單(已解決)
本帖最後由 totes 於 2011-12-18 20:56 編輯
需求為使用VBA方式,將DPARTNO欄位,指定TC字串開頭項目,製作成清單方式
1.結果如G6~G9範例 (只需TC開頭項目)
2.最終需求如G11自作成下拉選單
因另有其他用途,除樞紐分析以外,以上敘述有沒有辦法用VBA方式表達(第1點即可)
感謝了
[attach]8831[/attach]
作者:
register313
時間:
2011-12-18 13:33
回復
1#
totes
初學者VBA
Sub xx()
Range("G:G") = ""
I = 2
X = 1
Do While Cells(I, 5) <> ""
If (Cells(I, 5) Like "TC*") And (Range("G:G").Find(WHAT:=Cells(I, 5)) Is Nothing) Then
Cells(X, 7) = Cells(I, 5)
X = X + 1
End If
I = I + 1
Loop
End Sub
複製代碼
[attach]8832[/attach]
作者:
totes
時間:
2011-12-18 16:20
可以使用,真的感謝萬分^ ^
作者:
GBKEE
時間:
2011-12-18 18:35
回復
1#
totes
Option Explicit
Sub Ex()
Dim S As String, I As Integer, W As String
S = ""
I = 2
[G:G] = ""
Do While Cells(I, 5) <> ""
W = Trim(Cells(I, 5))
If W Like "TC*" And InStr(S, W & ",") = 0 Then
S = S & W & ","
End If
I = I + 1
Loop
If S <> "" Then
S = Mid(S, 1, Len(S) - 1)
'1.結果如G6~G9範例 (只需TC開頭項目)
[G1].Resize(UBound(Split(S, ",")) + 1) = Application.Transpose(Split(S, ","))
'2.最終需求如G11自作成下拉選單
With [G11].Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=S
End With
End If
End Sub
複製代碼
作者:
totes
時間:
2011-12-18 19:06
感謝版主,連下拉選單都有了,厲害!
作者:
Hsieh
時間:
2011-12-18 22:52
回復
5#
totes
不須使用範圍作清單
Sub ex()
Set d = CreateObject("Scripting.Dictionary")
C = InputBox("輸入開頭字元", , "TC")
ar = Range("E2", [E65536].End(xlUp)).Value
For Each a In ar
If a Like C & "*" Then d(a) = "" '不重複符合規則
Next
If d.Count = 0 Then MsgBox "無符合資料": Exit Sub
With Range("G11").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Join(d.keys, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.IMEMode = xlIMEModeNoControl
.ShowInput = True
.ShowError = True
End With
End Sub
複製代碼
歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)