Problems trying to set RefersToRange property in Excel VBA

I was helping a friend work out a problem with VBA in Excel 2007 today, and we ran into an issue I think I'd encountered and worked around in the past. It's an issue with changing the range to which a name refers.

On my friend's main worksheet, in B7, she has data validation from a List where the Source is a named range, CAT_LOOKUP. She wanted to run a sub that would filter a table on another worksheet to show only rows that correspond to the value in B7 and then use those rows as the source for validation in another cell on that worksheet.

Here's the relevant part of the VBA we were using:

Dim strCAT As String
Dim strACT As String
Dim sh As Worksheet
Dim rng As Range
Dim rngDest As Range

If Cells(7, 2) <> "" Then
    strCAT = Cells(7, 2).Value

    Sheets("CAT LOOKUP").Range("$A2:$C393").AutoFilter Field:=1, _
        Criteria1:=strCAT

    Set sh = Sheets("CAT LOOKUP")
    Set rng = sh.Range("B34:B56")
    rng.ClearContents
    Set rng = sh.Range(sh.Range("B1"), sh.Range("B1").End(xlDown))
    rng.Copy
    Set rngDest = sh.Range("B34")
    rngDest.PasteSpecial

    ActiveWorkbook.Names("CAT_LOOKUP").RefersToRange = _
        sh.Range(sh.Range("B35"), sh.Range("B35").End(xlDown))

Else
    Set sh = Sheets("CAT LOOKUP")
    Set rng = sh.Range("B34:B56")
    rng.ClearContents

    Sheets("Ad Hoc Request").Select
End If

CAT_LOOKUP is already defined. When this code is run, the CAT_LOOKUP range is cleared, and the definition of the range is unchanged.

I found in my notes from an old project that I'd used RefersToR1C1 instead of RefersToRange, so I changed that line to this:

    ActiveWorkbook.Names("CAT_LOOKUP").RefersToR1C1 = _
        "='CAT LOOKUP'!R35C2:R" & sh.Range("B35").End(xlDown).Row & "C2"

and the code worked as desired, resetting the named range so that the corresponding data validation works properly.

Is t开发者_C百科his simply a bug in the implementation of RefersToRange, or is there a problem with the way we were using it?

---

RefersToRange is read-only, at least in XL 2003 and probably in 2007.