VBA Data Validation Error

[toboggan]

Hello All,

This is my first post, so forgive me if my post is somewhat inept.

I'm having trouble with some VBA code. I've got some cells that I need to create a drop down list based on the selection in another drop down list. I can do this with no trouble via excel, but trying to create it again via VBA is not going well. I used the macro recorder for some guidance on what to do as far as coding and it provided the following:

With Range("BQ208").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = False
    End With

However, when I run the macro, I'm getting the following error: "Run-time error '1004': Application-defined or object-defined error"

I've googled the heck out of this question for the last couple days and can't seem to figure out what the problem is. When running the debugger, it's showing this line as being the issue:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"

Any help would be much appreciated.


Thanks!

 

[Jonmo1]

So the value in BQ207, is that a text string to represent a Named Range?
And is that Named range a Dynamic Named Range?

Unfortunately, Dynamic named ranges don't work with Indirect.

 

[toboggan]

The value in BQ207 is a named range. It refers to this: 'Drop-Down List'!$D$2:$D$5. As I said, it works when I do it on my own, but I can't get the VBA to make it happen for me.

 

[Jonmo1]

So the 'Refers To' box of the named range reads
='Drop-Down List'!$D$2:$D$5

Are you sure it's not a dynamic named range like
=OFFSET('Drop-Down List'!$D$2,COUNTA(....))
Or something similar?


You said it works if you do it on your own, so you mean you can go into Data Validation and select List and enter
=INDIRECT(SUBSTITUTE($BQ$207," ","_"))
And it works?


Does this work? (I'm asking only as a troubleshooting step, not possible solution)

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="'Drop-Down List'!$D$2:$D$5"

 

[toboggan]

So the 'Refers To' box of the named range reads
='Drop-Down List'!$D$2:$D$5

- Yes, this is correct.

Are you sure it's not a dynamic named range like
=OFFSET('Drop-Down List'!$D$2,COUNTA(....))
Or something similar?

- Nope. It is just that set range.

You said it works if you do it on your own, so you mean you can go into Data Validation and select List and enter
=INDIRECT(SUBSTITUTE($BQ$207," ","_"))
And it works?

- Correct

Does this work? (I'm asking only as a troubleshooting step, not possible solution)

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="'Drop-Down List'!$D$2:$D$5"

I tried putting that in and got the same error message for the same portion of the code.

 

[Jonmo1]

Sorry, that should have been

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Drop-Down List'!$D$2:$D$5"

 

[toboggan]

Sorry, that should have been

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Drop-Down List'!$D$2:$D$5"

I figured out the equals sign and had already tried that... still no luck. I answered the other ones but did it within the quotes. I went back and fixed it so it should be readable now. Sorry about that.

 

[toboggan]

Crud. Just noticed a typo of my own which made your suggestion work. (Mondays!) Hopefully that will get us a little closer to a solution.

 

[Jonmo1]

OK, so to clarify

This works

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="='Drop-Down List'!$D$2:$D$5"

This does NOT

 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(SUBSTITUTE($BQ$207,"" "",""_""))"

Right?

I can only think of two reasons
1. The named range is dynamic
2. The named range is misspelled in BQ207

 

[toboggan]

That's correct.

I don't think it's dynamic. It's always pulling from the same 4 cells every time and there is nothing that would change it.

I don't think it's misspelled... the previous code is set to just copy rows above it, however, since the cells have to refer to this specific cell as opposed to the cell that was copied, these changes have to be made. The drop down list in BQ207 that BQ208 is supposed to be drawing from works correctly.

That being said, because nothing is selected from BQ207, when I would do this on my own, I would have to acknowledge that using that cell as a reference would result in an error (because there is no value to search for). Could this be the issue?