Dependant Data Validation and Relative Referrence in Macros

DamienPalmer

New member
Joined
Feb 5, 2014
Messages
5
Reaction score
0
Points
0
Hi,
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
 
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,"" "",""_""))"
 
Hi Bob, thanks for you reply.

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

Code:
ActiveCell.Select
    With Selection.Validation
        .Delete
        .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.

Damien
 
Damien,

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.
 
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!!

Damien
 

Attachments

  • Macro Test.xlsm
    257.4 KB · Views: 29
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"
 
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.

Damien
 
Post your workbook so we can see the actual problem.
 
Hi Bob,

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

Thanks

Damien
 

Attachments

  • Macro Test.xlsm
    252 KB · Views: 36
Back
Top