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:

=OFFSET(Sheet3!R2C5,0,0,COUNTA(Sheet3!C5)-1,2)

.

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

=OFFSET(Sheet3!$E$2,0,0,COUNTA(Sheet3!$E:$E)-1,2)

I have the following subs:

option explicit
Const rowstart As Integer = 3

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Columns(3)) Is Nothing Then
        On Error GoTo 0
        fillinfunctions 3, 6, "=vlookup(RC[-3],shtablerange,1,0)" 'This last parenthesis kind of helps.
    End If
End Sub

Private Sub fillinfunctions(colchange As Integer, colindex As Integer, theformula As String)
    Dim therange As Range
    Dim rowcount As Long
    Dim i As Integer
    rowcount = Cells(65536, colchange).End(xlUp).Row 'This is where I get the 1004 error.
    If rowcount > rowstart - 1 Then
        Set therange = Range(Cells(rowstart, colindex), Cells(rowcount, colindex))
        therange.FormulaR1C1 = theformula
    End If
End Sub

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!