|
Set_list はこのようにした方がわかりやすいので訂正します。
>UserForm1モジュール
>Private Sub ListBox1_Click()
> ActiveCell.Value = Me.ListBox1.Text
> Set_list
> DoEvents
> Application.Visible = True
>End Sub
>
>Private Sub UserForm_Initialize()
> With Me.ListBox1
> .ColumnCount = 2
> .ColumnWidths = "30;30"
> .TextColumn = 1
> End With
> Set_list
>End Sub
>
>Sub Set_list()
>Dim a
>Dim dic As Object
>Dim myRange As Range, r As Range
>
> Set dic = CreateObject("Scripting.Dictionary")
> ReDim a(1)
> dic("コマ") = "個数"
For i = 0 To 10
dic(Chr(Asc("a") + i)) = 0 'コマ
Next
> Set myRange = Range("a1:z30")
> For Each r In myRange
> If r.Text <> "" Then
> dic(r.Text) = Application.CountIf(myRange, r.Text) 'key別個数
> End If
> Next
>
> '-----個数出力
> With UserForm1.ListBox1
> .Clear
> .List = Application.Transpose(Array(dic.keys, dic.Items))
> End With
> Set dic = Nothing
>End Sub
|
|