Help with Macro/Data Validation

[demonfreak]

i'm needing help with a macro I'm trying to create to save me some time on creating dependent data validations.

So it looks like the macro I created works with a normal named range but when I use the formula "indirect" it's giving an error, if I enter in any other named range i don't get the error, this wouldn't work because it needs to be a depending data validation drop list.

Any help will be appreciated.

    Sheets("D2D").Select
    Range("AG2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=Error"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("AH2").Select
    With Selection.Validation
        .Delete
[B][COLOR=#ff0000]'THIS IS WHERE THE ERROR IS[/COLOR][/B]
[B]        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _[/B]
[B]        xlBetween, Formula1:="=INDIRECT(AG2)"[/B]
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Range("AI2").Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(AH2)"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With

Thanks in Advance!

 

[baitmaster]

welcome to the board

I can see the benefit of using a dependent DV list, but I don't see why it needs to be set using a macro. Why can't you just configure it manually? If you want something that changes using VBA during runtime, then you could probably avoid the dependency altogether. Can you elaborate on your requirement a little bit?

I note when recording this macro I get a message stating that my range currently evaluates to an error, do I wish to continue? Usually you can get around these types of issue by suppressing messages, but sadly it seems not in this case

My workaround would probably be to create the individual DV lists based on worksheet_change events, so only when the previous option has been selected

 

[demonfreak]

The main reason I want it to be macro set up is because I have a daily report that requires this, it's essentially a repetitive task that I want to eliminate by running a macro.

 

[baitmaster]

OK so you are writing an entire file from scratch, and want to build this functionality from the bottom up when you do?

How about having most of it already set up in a template - say a worksheet already in the file containing the code, that is then used to generate the file by a more simple move/copy? It would be easier to make amendments to the file, and your code would be simpler. Plus you'd get around your current problem