VBAで重複しないデータ（コレクション）の入力規則を指定。

有る表の列から「重複しないデータ」を抽出し、これをコレクションに格納して、データの入力規則のドロップダウンに表示させたいです。

セルの1E（Cells（1,5））をクリックした時に、データ（）を表から抽出し表示させる仕組みとしたく、以下の様なコードを作成したましたが、データの入力規則の配列に作成したコレクション（Data）を反映させたいのですがうまく行きません。
（「アプリケーション定義又はオブジェクト定義のエラーです。」と表示されます。）

以下がそのコードです。※該当部に「ここです！」と表示しました。

    If ActiveCell.Row = 1 And ActiveCell.Column = 5 Then
     Dim Data As New Collection
     Dim intD As Long
     
     intD = Cells(3, 2).End(xlDown).Row
     
     On Error Resume Next
       For i = 3 To intD
        Data.Add Cells(i, 2).Value, Cells(i, 2).Value
       Next i
         On Error GoTo 0
         
           With Cells(1.5).Validation
               .Delete
               .Add Type:=xlValidateList, Operator:=xlEqual, _
               Formula1:=Data'ここです！
          End With
     End If

コレクションを反映させることは不可能でしょうか。
他の方法や解決策等が在りましたらご教授いただければ幸いです。

よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

コレクションを反映させることは不可能でしょうか。

不可能です。

カンマ区切りのテキストに変換してから、Formula1: に設定してください。

コード例

vba
1    Dim strData As String, i
2    For Each i In data
3        strData = strData & "," & i
4    Next
5    strData = Mid(strData, 2)
6
7    With Cells(1.5).Validation
8        .Delete
9        .Add Type:=xlValidateList, Operator:=xlEqual, _
10             Formula1:=strData 
11    End With