У меня есть ячейка с выпадающим списком (используя "Проверка данных").
Список поступает из именованного диапазона в другом листе.
Я хочу, чтобы раскрывающийся список показывал именованный диапазон и другое предопределенное значение. Так, например, если диапазон выглядит так:
1
2
3
4
Я хочу, чтобы раскрывающийся список показывал:
1
2
3
4
17 (my predefined value)
Как это сделать?
Если вы хотите использовать данные проверки для создания списка, это невозможно, вы получите сообщение об ошибке:вы не можете использовать ссылочные операторы (такие как объединения, пересечения и диапазоны) или константы массива для критериев условного форматирования.
Поэтому я предлагаю вам попробовать использовать код в качестве обходного пути, чтобы помочь вам решить эту проблему.
Sub SetDataValidation() '
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
Dim rng As Range
'get range of named range
Set rng = WS.Range("NUMBER")
Dim ARR As Variant
ReDim ARR(1 To 1) As Variant
'add cell value from named range to an array
For i = 1 To rng.Cells.Count
ARR(UBound(ARR)) = rng.Cells(i).Value
ReDim Preserve ARR(1 To UBound(ARR) + 1) As Variant
Next i
'add your predefined value to the array
ARR(UBound(ARR)) = 17
'set validation for B1 with the combined array
With WS.Range("B1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(ARR, ",")
End With
End Sub
В моем примере я назвал диапазон как NUMBER и получил результат:
У меня была похожая проблема с твоей. У меня был именованный диапазон Dyn_ParamList_KillDate со списком дат 1/1/2018, 1/2/2018, etc... . Д. Этот динамический именованный диапазон заполняется процессом и изменяется в размере. Проблема иногда в том, что список будет пустым, чтобы выпадающий список вызывал проблемы.
Поэтому для своего решения я создал еще один диапазон Range_Writeback_ModelParamDate который всегда имеет значение. Затем я изменил список проверки данных FROM =Dyn_ParamList_KillDate TO =IF(Dyn_ParamList_KillDate<>"", Dyn_ParamList_KillDate, Range_Writeback_ModelParamDate) .
Как только я добавил этот второй диапазон, удостоверился, что он всегда имел значение, и изменил формулу выпадающего меню, чтобы использовать IF и тянуть из любого диапазона - я начал правильно получать результаты, основанные на двух диапазонах. Я надеюсь, что это помогает кому-то.
Идея для этого решения пришла отсюда: https://www.mrexcel.com/forum/excel-questions/754454-you-may-not-use-reference-operators-array-constants-data-validation.html