vba data validation with if statement

[yichuansancun]

i have the following codes:

Sub GSDDropDown()
If Range("AssignmentType").Value = Range("A1") Then
Range("EntrySub").Validation.Add Type:=xlValidateList, Formula1:="=GSD_Sub_DropDown"
Else
Range("EntrySub").Value = "N/A"
End If
End Sub

it is giving me Run-time Error 13 - Type mismatch. what did i do wrong?

P

 

[Jerry Sullivan]

I can't tell from your code why you are getting a Run-time Error 13.
You should add a .Delete statement otherwise you will get an Error 1004 if you try to run the code when the cell already has Data Validation.

Sub GSDDropDown()
 If Range("AssignmentType").Value = Range("A1") Then
    With Range("EntrySub").Validation
[COLOR="#0000CD"][B]        .Delete[/B][/COLOR]
        .Add Type:=xlValidateList, Formula1:="=GSD_Sub_DropDown"
    End With
 Else
    Range("EntrySub").Value = "N/A"
 End If
End Sub

When I mock up an example, this works fine.

Assuming that GSD_Sub_DropDown is a Name, what is listed in the Refers to: field for this Name in the Name Manager?

What line of code is the debugger highlighting when the error occurs?

 

[Jerry Sullivan]

One other possibility...

You'll get a Run-time Error 13 - Type mismatch if either
Range("AssignmentType") or Range("A1") evaluate to errors.

Try this instead to handle that scenario:

Range("AssignmentType").Text= Range("A1").Text

 

[Michael M]

Dupe post
http://www.mrexcel.com/forum/showthread.php?647485-why-is-this-not-working

 

[VoG]

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule #9 here: http://www.mrexcel.com/forum/showthread.php?t=99490).