也可以利用數組
也可以利用ADO
來實現
用字典幫妳做壹個
Sub OkExcelDictionary()
'智能Excel網站提供
Dim d As Object
Dim WeekStr As Variant
Dim r As Range
Dim i As Integer
Set d = CreateObject("Scripting.Dictionary")
Set r = Worksheets(1).Range("A1")
WeekStr = Split("星期壹,星期二,星期三,星期四,星期五,星期六,星期日", ",")
Do While r.Text <> ""
If d.exists(r.Text) Then
d.Item(r.Text) = d.Item(r.Text) & "," & r.Offset(0, 1).Text
Else
d.Add r.Text, r.Offset(0, 1).Text
End If
Set r = r.Offset(1, 0)
Loop
Set r = Worksheets(2).Range("A1")
For i = 0 To 6
r.Value = WeekStr(i) & IIf(d.exists(WeekStr(i)), "," & d.Item(WeekStr(i)), "")
Set r = r.Offset(1, 0)
Next
d.RemoveAll
Set r = Nothing: Set d = Nothing
End Sub