Using a named range in a header with VBA?

S

slingsh0t

I'd like to apply a named range to headers in a file. I did a search
and found some syntax that is close, but still generating an error.
The named range is "DollarVal". The code I have is

ActiveSheet.PageSetup.RightHeader = "Total: " & Range(DollarVal)

Any thoughts? Thanks!
 
B

Bob Phillips

You need to enclose the name in quotes.

But if you want a total, shouldn't you sum it as well?

ActiveSheet.PageSetup.RightHeader = "Total: " &
Application.SUM(Range("DollarVal"))


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

slingsh0t

Bob- Thanks for such a quick response. I still get an error:
Run-time error '1004':
Method 'Range' of object '_Global' failed

Am I missing something?
 
B

Bob Phillips

Is it NG wrap-around?

Try

ActiveSheet.PageSetup.RightHeader = "Total: " & _
Application.SUM(Range("DollarVal"))


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
S

slingsh0t

It's not a word-wrap issue; I have it all on one line.

As a test I closed Excel and re-started; I created the named range and
started a new macro with only one line (the one you posted). That
generated the same error when I ran it.

I looked at the MS Knowledge Base and found an article
http://support.microsoft.com/default.aspx?scid=kb;en-us;319832
that talks about unqualified references in VB (not VBA). Could this be
a similar situation?
 
B

Bob Phillips

It doesn't look related to me. A long-shot, but check if you have any
missing references (Tools>References), uncheck them if so.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top