Named Range throwing error 1004

Hi All,

I have an event macro for my worksheet that automatically populates a formula when a certain part of my worksheet is changed. This formula calls a reference to a named range called shtablerange.

shtablerange is a dynamic named range defined by the offset formula:

Please Login or Register  to view this content.

.

Here's the same dynamic named range formula in CR format:

Please Login or Register  to view this content.

I have the following subs:

Please Login or Register  to view this content.

I have been able to succesfully use dynamic named ranges in conjunction with formular1c1 in the past (and in fact, am succesfully using it in other parts of this macro using specifically the fillinfunctions private sub). I have checked the case of my named range (all lowercase), and have also confirmed that variable therange is properly being set (using a .copy method). Furthermore, if (in R1C1 view) I copy and paste the formula from VBA into a cell in Excel, it works perfectly as intended. Finally, note that this same function works perfectly well when accepting other arguments.

I have tried replacing vlookup with a user-defined function that can accomplish the same thing (and that seems to work okay in other parts of my code), but I still get the 1004 error. I'm suspecting it's something wrong with my named range, but I'm not sure what.

I have also verified that my named range does populate into a range (i.e. it's not nothing).

Any help would be greatly appreciated.

Thanks.

Edit--

I figured it out. While Excel has no problem adding the last parenthesis for me, apparantly VBA does. Doh!