Problems with name-range or group

[espenskeie]

Hi

I try to create a dynamic name group, but i am stuck.

This my code, or what I have tried to write:

ActiveWorkbook.Names.Add Name:="A2" & "_Exchange", RefersToR1C1:= _
        "=" & "A2" & "!R2C1:R" & lr2 & "C1"
    ActiveWorkbook.Names((ActiveSheet.Range("A2")" & "Exchange").Comment = ""

This line is bugging:

ActiveWorkbook.Names((ActiveSheet.Range("A2")" & "Exchange").Comment = ""

And this is the code without dynamic try:

ActiveWorkbook.Names.Add Name:="Year", RefersToR1C1:= _
        "=" & "A2" & "!R2C4:R" & lr3 & "C4"
    ActiveWorkbook.Names("Year").Comment = ""

Regards
Espen

 

[Norie]

Espen

What name are you trying to use for the range?

The first part of the code would make a range called A2_Exchange which refers to a range on a worksheet called 'A2'.

Here's what the range looks like when I use 100 for lr2.

='A2'!$A$2:$A$100

 

[espenskeie]

Hi Norie

I think I just found a workaround, I'm just about to finish the writing, so far it looks good:

Name1 = ActiveSheet.Range("A2") & "_Exchange"
    
''' Lager et "Name" for gruppen Exchange slik at den blir dynamisk
    ActiveWorkbook.Names.Add Name:=Name1, RefersToR1C1:= _
        "=" & "A2" & "!R2C1:R" & lr2 & "C1"
    ActiveWorkbook.Names(Name1).Comment = ""

Regards
Espen

 

[AlphaFrog]

Try something like this...

[color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range

ActiveWorkbook.Names.Add Name:=Range("A2").Value & "_Exchange", RefersToR1C1:= _
        "=" & Range("A2").Value & "!R2C1:R" & lr2 & "C1"
        
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range(Range("A2").Value & "_Exchange")
        [color=darkblue]If[/color] [color=darkblue]Not[/color] (cell.Comment [color=darkblue]Is[/color] [color=darkblue]Nothing[/color]) [color=darkblue]Then[/color] cell.Comment.Text " "
    [color=darkblue]Next[/color] cell
    
ActiveWorkbook.Names.Add Name:="Year", RefersToR1C1:= _
        "=" & Range("A2").Value & "!R2C4:R" & lr3 & "C4"
        
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Range("Year")
        [color=darkblue]If[/color] [color=darkblue]Not[/color] (cell.Comment [color=darkblue]Is[/color] [color=darkblue]Nothing[/color]) [color=darkblue]Then[/color] cell.Comment.Text " "
    [color=darkblue]Next[/color] cell

 

[espenskeie]

Thanks AlphaFrog

I found something that did work halfway:

Name1 = ActiveSheet.Range("A2") & "_Exchange"
    Name2 = ActiveSheet.Range("A2") & "_Year"
    Name3 = ActiveSheet.Range("A2") & "_Month"
    Name4 = ActiveSheet.Range("A2") & "_Day"
    Name5 = ActiveSheet.Range("A2") & "_Frequence"

    ActiveWorkbook.Names.Add Name:=Name1, RefersToR1C1:= _
        "=" & "A2" & "!R2C1:R" & lr2 & "C1"
    ActiveWorkbook.Names(Name1).Comment = ""
    
''' Lager et "Name" for gruppen Year slik at den blir dynamisk
    ActiveWorkbook.Names.Add Name:=Name2, RefersToR1C1:= _
        "=" & "A2" & "!R2C4:R" & lr3 & "C4"
    ActiveWorkbook.Names(Name2).Comment = ""
    
''' Lager et "Name" for gruppen Month slik at den blir dynamisk
    ActiveWorkbook.Names.Add Name:=Name3, RefersToR1C1:= _
        "=" & "A2" & "!R2C5:R" & lr4 & "C5"
    ActiveWorkbook.Names(Name3).Comment = ""
  
    ActiveWorkbook.Names.Add Name:=Name4, RefersToR1C1:= _
        "=" & "A2" & "!R2C6:R" & lr5 & "C6"
    ActiveWorkbook.Names(Name4).Comment = ""
    
    ActiveWorkbook.Names.Add Name:=Name5, RefersToR1C1:= _
        "=" & "A2" & "!R2C7:R" & lr6 & "C7"
    ActiveWorkbook.Names(Name5).Comment = ""

Right now I have two workbooks, AMEX and NYSE. When this code is done, I only have names for NYSE.

I ran it manually, and stopped when AMEX was done, and checked "Namemanager". And all the names was there.

It seems like the code overwrites the second time. Even though the names are different....

Regards
Espen

 

[espenskeie]

Hmm, I just wanted to tell you that I messed up the code on my own. I found the error....

I forgot to put the "delete all names" code outside of the loop. Thats why I allways ended up with only names for the last loop.

I'll take 100 pushup as penalty...