Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
On Aug 8, 1:18 pm, geebee (noSPAMs) wrote:
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
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. -- HTH... Jim Thomlinson "geebee" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
change named range
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????) geebee wrote: 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named range in chart - any way to change the workbook reference with VBA | Excel Programming | |||
Named Range Change Based On AutoFilter | Excel Programming | |||
Change event triggered by a named range | Excel Programming | |||
Change Event on a named range | Excel Programming | |||
Change named range value | Excel Programming |