Indirect Not Working For Data Validation

[mayoung]

I have been using this code for Data Validation for some time, but now when I try to use it, it throws a Run Time Error '1004' Application-Defined or Object-Defined Error. How can I change this to make it work again. I am using Office 365.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=INDIRECT(" & Cur_Cell & ")"

I am pretty sure this is the part of the code causing the issue:

Formula1:="=INDIRECT(" & Cur_Cell & ")"

Thanks In Advance For Any Help...

 

[Yongle]

Read this old thread https://www.mrexcel.com/forum/excel...-erroneous-list-formula-validation-vba-2.html

VBA is encountering the equivalent of "The source currently evaluates to an error" which you see when DV added manually
- Excel then helpfully offers option "Do you want to continue?" (which forces DV to be added)
- VBA does NOT have that option

The error is probably happening because Curr_Cell is empty, but woud also occur if its value is not valid
- the formula evaluates to an error and is rejected

Workaround
- test the value in Cur_Cell
- if that value is invalid (or empty), replace with a "Dummy" valid value (to force DV to be accepted)
- add DV
- if Curr_Cell = "Dummy" clear its contents