Data Validation using if formula

Question

Hi All,

I'm trying to add a data validation to a range shows below code

ThisWorkbook.Names(TargetRangeName).RefersToRange.Validation.Add _

            Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= \_ 

                xlBetween, Formula1:="=IF(LEFT(M\_SenderCompanyCode,4)=""PK01"",ARGL8999,ARGLPAK)"

where

1. TargetRangeName is a named range where we should add validation list

2.M_SenderCompanyCode is a named range of a single cell address

3. A table 1 is their which one column is named as a range "ARGL8999"
4. A table 2 is their which one column is named as a range "ARGLPAK"

the error pop up is

Run-time error '1004'

But when i add this formula(=IF(LEFT(M_SenderCompanyCode,4)="PK01",ARGL8999,ARGLPAK)) in excel data validation list it works but through code only it doesn't work.

Answer 1

Range names not used in formulas need to be in quotes:

ThisWorkbook.Names("TargetRangeName").RefersToRange.Validation.Add _

Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _

xlBetween, Formula1:="=IF(LEFT(M_SenderCompanyCode,4)=""PK01"",ARGL8999,ARGLPAK)"