VBA | Run time error for macro - set inputrange

Question

Hello everyone,

My team built a macro in Excel 2010 that we need to get to work on Excel 2007. It works great on 2010 and 2013, but in 2007 we get what I'm realizing is a classic error message: Runtime error 1004: Application-defined or object-defined error

When we hit "debug," one line of code is highlighted: Set inputRange = Evaluate(dvCell.Validation.Formula1)

I appreciate any help so so so much! I'm very much a VBA novice so I've been really struggling to decipher everything and understand what is going wrong. Thank you in advance, 

Rebecca

Answer 1

An unusual one.  I think we're going to need to see the data validation rule for the cell in question.  So you need to look at the code and find out what cell dvCell is.  You can use the immediate window to do so - let it go into debug and in the Immediate Window type:

? dvCell.Address [Enter]

that will give you the cell address, and then to find out what sheet it is on, type

? dvcell.parent.name[Enter] and that will give you the sheet name

finally you can find out what that formula is by typing

? dvcell.validation.formula1[Enter]

And it is the .formula1 information that we pretty much need to see and then we can try to figure out why things aren't working.  The cell address and sheet name are actually kind of extra information that may or may not play into it later.

Now if the data validation rule is to get a list or address, then the evaluate() formula is probably going to result in that address. 

I set this code up in both Excel 2007 and 2010 on 2 separate machines and it actually works fine for me; myRange gets set to a reference to $K$1:$K$4 --

Sub Test()

  Dim dvCell As Range

  Dim myRange As Range

  'Range G2 on the sheet has data validation

  'set to a list with the formula being

  ' =$K$1:$K$4

  Set dvCell = ActiveSheet.Range("G2")

  Set myRange = Evaluate(dvCell.Validation.Formula1)

  'at this point myRange will reference range K1:K4

  'for me this works in both Excel 2010 and 2007

End Sub