VBA convert offset formula to range object

[hobgoblinvasya]

Hi all,

i am looking for a way to convert a cell's formula to a range object. Lets say i have a have that have a dynamic validation list though an offset formula. I need to loop an ensuing list to get all its values. Would anyone know, how would i achieve this through VBA.

ex: cell A1 has a similar formula to what's on this page:
http://www.contextures.com/xldataval02.html
http://www.contextures.com/DataValDynamic.zip (sample file)

I need to loop all the values in the dependent list when i choose one of the values on the master list.

So far i got to the part where i can extract the underlying formula that links dynamically to appropriate named range. something like (=OFFSET(INDIRECT(INDEX(RegionListLookup,MATCH(A2,RegionList,0))&"1"),0,0,COUNTA(INDIRECT(INDEX(RegionListLookup,MATCH(A2,RegionListe,0))&"1List")),1))

Now i need to extract the range that this will point to dependent on what is chosen in cell A2.

As usual thanks in advance !

 

[hobgoblinvasya]

Having analyzed it some more i think it comes down to this:

I need to transform this formula into a range via VBA:
=OFFSET(Sheet1$T$110,0,0,3,1)

I tried using Evaluate("=OFFSET(Sheet1$T$110,0,0,3,1)"), but i get an error 2015.
If anyone know how to do it, it would be a huge help!

 

[hobgoblinvasya]

OK, one final update. Apparently Evaluate does not like when the formula is in another language. When converted to English it seems to work ...

Thanks again everyone. Hope this will be helpful to someone else in the future