change named range

[geebee]

hi,

i am trying to yield/get at this new range programmatically:
=lookup!$AB$14:OFFSET(lookup!$AB$14,lookup!$AC$100,0)

by using:
ActiveWorkbook.Names.Add Name:="MONTH_SOURCE2", RefersToR1C1:= _
"='lookup'!R1C28:OFFSET('lookup'!R1C28, 'lookup'!R" & NEWMO3 & "C29"

but i cant figure out what is wrong with my syntax.

thanks in advance,
geebee

 

[eholz1]

hi,

i am trying to yield/get at this new range programmatically:
=lookup!$AB$14:OFFSET(lookup!$AB$14,lookup!$AC$100,0)

by using:
ActiveWorkbook.Names.Add Name:="MONTH_SOURCE2", RefersToR1C1:= _
"='lookup'!R1C28:OFFSET('lookup'!R1C28, 'lookup'!R" & NEWMO3 & "C29"

but i cant figure out what is wrong with my syntax.

thanks in advance,
geebee

Guess number one: I think you might need parens around your lookup
functions??

= lookup(parameters,range) I never us R1C1 notation so you may be able
to omit parens using that syntax.

eholz1

 

[Jim Thomlinson]

it is kinda difficult to determine exactly what you want from your post so
here is generally one way to do named ranges

dim rng as range

with sheets("Lookup")
set rng = .range(.range("AB1"), .cells(rows.count, "AC").end(xlup))
end with

Thisworkbook.names.add Name:="MONTH_SOURCE2", RefersToRange:=rng

This creates a named range from AB1:AC?? where ?? is the last populated row
in AC.

 

[Dave Peterson]

I don't see that last comma, zero and close paren: ,0)
in your code.

ActiveWorkbook.Names.Add Name:="MONTH_SOURCE2", RefersToR1C1:= _
"='lookup'!R1C28:OFFSET('lookup'!R1C28, 'lookup'!R" & NEWMO3 & "C29,0)"

 

[geebee]

hi,

i want to be able to know how to change the named range while also adding
the header row in row 1. for example, if the new range is like rows 56 - 99,
this new range does not include the header row, so i cant use it as a graph
source. what do i need to do?

thanks in advance,
geebee

 

[Dave Peterson]

I would copy the header to a new worksheet, then copy the rows I want under that
header on the new sheet.

(If I understand correctly????)