Outliers are missing when using Range formula
I have the following VBA function. This is required to create a dropdown list in the specified cells.
Public Sub CORE_SetValidation(ByRef Rng As Range, ByVal Value As String)
With Rng.Validation
Call .Delete
If Value <> "" Then
Call .Add(Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Value)
.ErrorMessage = "Please select a value from drop-down list"
.ErrorTitle = "Value Error"
.IgnoreBlank = True
.InCellDropdown = True
.InputMessage = ""
.InputTitle = ""
.ShowInput = True
.ShowError = True
End If
End With
End Sub
When I pass values to this comma delimited value its a working fine for all columns
dictkeystring = "1,2,3,4,5,6,7,8,9"
CORE_SetValidation(Sourcews.Columns(AllocationSheet_Prj_COLUMN).EntireColumn, dictkeystring)
But when I pass the values using the Range formula it doesn't work.
dictkeystring = "=PrjList!A2:A6"
CORE_SetValidation(Sourcews.Columns(AllocationSheet_Prj_COLUMN).EntireColumn, dictkeystring)
The problem is that I am missing one value for each cell; The first column is missing one val, the second cell is missing 2 val, for the third one is missing 3 values, ... after some place I skip all the values, an empty dropdown I get
I am not sure why this error occurs, can I resolve it in the range formula
source to share
Try
"=PrjList!$A$2:$A$6"
Without a $
specific address, the range is "relative", so it will "slide" down with the cells in your range. i.e. the fr check in the second line will be A3:A7
, etc.
$
in the address tells Excel to correct the validation range because it is an absolute address.
source to share