Drop_Box_Insert_Error

I have a problem inserting a drop box choice list in a cell using the values of a predefined array. Array name is Local_Cell_ID_List

Here I got the unique values from sheet Arranged

set Arranged=thisworkbook.worksheets("Arranged_Data")
   Dim f, addr$, g&
  With Arranged
   addr = .Name & "!$H$2:" & .[H1].End(xlDown).Address
  End With
  With Synthese_Global
    f = Application.Evaluate("=LET(a," & addr & ",u,UNIQUE(a),HSTACK(u,XMATCH(u,a)))")
    g = UBound(f, 1): .[A2].Resize(g, 2) = f
 End With

so now I want to put the unique values i got now in worksheet Synthese_Global in range A2, i want them to be in choice list in cell B1 for which i tried

redim Local_Cell_ID_List(0 to Range("A2").End(xlDown).Row-1)
Local_Cell_ID_List=application.transpose(range("A2"))

so after that I tried to insert the choice list as:

    With Synthese_Global.Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Local_Cell_ID_List"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
  .ErrorMessage = ""
  .ShowInput = True
   .ShowError = True
 End With

but i have the error "Type mismatch" at line

Local_Cell_ID_List =Application.Transpose(Synthese_Global.Range("A2"))

This is from Microsoft

xlValidateList: Formula1 is required, Formula2 is ignored. Formula1 must contain either a comma-delimited list of values or a worksheet reference to this list.

According to this a workaround to generate a text string with the calculated values of the actual result, and set the Formula1 parameter to this string.

This is a sample code of it:

Sub validator()
Set ws = ActiveSheet
Dim a(), str
a = [a22:a24 * B22:B24]   'for simulate a result array

   'Create string of actual values

For i = 1 To UBound(a)
  If Len(str) = 0 Then
    str = a(i, 1)
  Else
    str = str & "," & a(i, 1)
  End If
Next i

ws.Range("A32").Validation.Add xlValidateList, , , str
End Sub

Adapting to actual values:
Let assume that validation list is in column A.
The dropdown required in cell B1.

Adapted code

Sub validator()
Set ws = ActiveSheet
Dim str As String
   'Create string of actual values

ws.Range("B1").Validation.Delete
ws.Range("B1") = Empty
For i = 2 To ws.Range("A2").End(xlDown).Row
  If IsError(ws.Cells(i, 1)) Then 'ignore error values in the list. change to: If ws.Cells(i, 1)="N/A" Then
  Else                                    'if it is real string
    If Len(str) = 0 Then
      str = ws.Cells(i, 1)
    Else
      str = str & "," & ws.Cells(i, 1)
    End If
  End If
Next i

ws.Range("B1").Validation.Add xlValidateList, , , str
End Sub

Replace ws.Cells(i, 1) with the desired calculation for the cell. e.g. call a function process(ws.Cells(i, 1)). Obviously in case of error input the process function must handle the error and the valid data too.

Example:

Function process(b As Variant) As Variant
If IsError(b) Then process = b: Exit Function
process = "My " & b
End Function