Results 1 to 9 of 9

Thread: Dependant Data Validation and Relative Referrence in Macros

  1. #1

    Dependant Data Validation and Relative Referrence in Macros

    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am new to using Macros and data validation and have run into a problem i am hoping someone can help with.
    I am trying to create a macro for estimating purposes that will contain a dependent data validation. The dependent validation will work if i use a direct reference in the second validation
    =INDIRECT(SUBSTITUTE(I82," ","_"))
    This allows my second selection based on the first.
    The problem i have is i want this to run within a macro that can be inserted at any location in the sheet and so the I82 would need to be relative based on where i start. If i try
    =INDIRECT(SUBSTITUTE(R[-15]C," ","_"))
    I get an error as i am trying to run the macro.
    How would be the best way to achieve this?
    Thanks Damie

  2. #2
    Are you setting a cell's formula in your VBA? If so, you should double up the quotes, like so

    Activeell.FormulaR1C1 = "=INDIRECT(SUBSTITUTE(R[-15]C,"" "",""_""))"

  3. #3
    Hi Bob, thanks for you reply.

    This is the code for the data validation i have in my macro

        With Selection.Validation
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="=INDIRECT(SUBSTITUTE(R[-15]C,"" "",""_""))"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = "Paver buildup"
            .ErrorTitle = ""
            .InputMessage = "Select build up type"
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
    This gives me a runtime error '1004'

    If i change the R[-15]C reference to an absolute reference (I82, or where ever the 1st data validation may be) the 2nd validation list works as it should. As all of my jobs are different this macro will not always be run and when it is it will not always run from the same position is i need the second validation (the above code) to always reference 15 rows up.


  4. #4

    Is there a valid value in that cell. Try inputting the DV within Excel, if it says it evaluates to an error, this will cause the VBA to bomb.

  5. #5
    Hi Bob,

    The value in that cell is valid. It works with a absolute reference, just not a relative one when running the macro. I have attached a sheet with the macro and an example of how it should work. Any help would be greatly appreciated!!

    Attached Files Attached Files

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Utah, USA
    I don't think you can use a R1C1 refference in the substitue.

    try using this

    Formula1:="=INDIRECT(SUBSTITUTE(char(96+column())&row()-15,"" "",""_""))"

    just a warning this will only work if you don't go into a column past Z. it won't work for AA for example. char(97) returns "a"

  7. #7
    Hi Simi,

    This will give me the relative reference i am looking for, but the 2nd validation drop down just gives me a repeat of the selection from the 1st one and not the named range. Choosing Pedestal Base in the 1st will hopefully display the range called Pedestal_Base as a drop down in the second.


  8. #8
    Post your workbook so we can see the actual problem.

  9. #9
    Hi Bob,

    Here is the workbook with an example of how i would like it to work.


    Attached Files Attached Files

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts