Named Ranges in VBA (Excel 2007)

Topic

New Reply

Have you tried:
FemaleValue= Range(“BG_F”).Value

Also instead of using the ADD NAMES, it is often easier to just use the range object:
Range(“D” & NextRow + 1).Name:=”BG_F”

Steve

I’ve used named range formulas for years with various syntaxes such as the following line from within a With Names grouping.

.Add Name:=”MudInAnnulusVariations”, RefersTo:=”=offset(‘Mud Circ Temps’!$B$9,0,9,COUNTA(‘Mud Circ Temps’!$B:$B)-5)”

When I attempt to use the shorter syntax as you show in this previous thread here, I get a “Compile Error” “Expected: expression” dialog box.  I’ve added Activeworkbook, ActiveSheet, Application and nothing eliminates that error.

I am running Windows 7 Ultimate 64 with Office 2007 Professional with SP2.

Any clues?

Mark Trevithick

Reply | Quote

Replies

Rather than re-opening a 3-year old thread, this post has been cross-referenced and made the subject of a new thread.

Reply | Quote

I don’t think Range(“BG_F”).Value is valid, there is no Value for the entire range. You would need an offset into the range to return a value.
Please you post the exact code?

cheers, Paul

Reply | Quote

The simpler method is for “naming a range” (that is giving a name to a formula that is refers to a range).

You appear to be trying to name a formula that does not refer directly to a range and thus the “Add Name” syntax must be used

Steve

Reply | Quote