#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2009
    Posts
    2
    Rep Power
    0

    Defiine a Named Range in Excel and capture the Values


    Can anybody help with the following: I'm trying to use a For Loop and if statements to go through cells and name ranges when a certain cell changes value. I have everything working correctly except for that once I'm in Excel there are no Values attached to the defined names I am not able to hyperlink to them for that reason.

    Here is the code:
    Dim Row As Integer
    Dim Hold As String
    Dim StartRow As Integer


    StartRow = 3


    For Row = 3 To workbook.ActiveSheet.UsedRange.Rows.Count + 1
    If Hold = "" Then
    Hold = workbook.ActiveSheet.Cells(Row, 1).Value

    ElseIf workbook.ActiveSheet.Cells(Row, 1).Value <> Hold Then
    workbook.ActiveSheet.Cells(Row, 1).Select
    If RangeName_Exists("WC" & Hold, workbook) = False Then
    workbook.Names.Add "WC" & Hold, "=Active Items!$a$" & StartRow & ":$BA$" & Row - 1
    workbook.Names("WC" & Hold).Value = "Active Items!$a$" & StartRow & ":$BA$" & Row - 1
    Else
    workbook.Names("WC" & Hold).RefersToR1C1 = "Active Items!$a$" & StartRow & C53 & Row - 1
    End If
    Hold = workbook.ActiveSheet.Cells(Row, 1).Value
    StartRow = Row
    If Hold = "" Then


    End If
    End If

    Next Row

    Please help if you have any idea what I am missing or if you need any other info!

    thanks,
    Mike
  2. #2
  3. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Have you step debugged?

    What is this line supposed to do (not familiar with .RefersToR1C1):
    workbook.Names("WC" & Hold).RefersToR1C1 = "Active Items!$a$" & StartRow & C53 & Row - 1
    What is the C53? If need the literal string put in quotes, if need contents of cell C53 need .Range("C53"). If you want to build a range where is ':'
  4. #3
  5. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Jul 2009
    Posts
    2
    Rep Power
    0
    I have stepped debugged but it is almost actually doing what I want it to do...The C 53 is just setting the range of how far to set the named range for.

    Where the problem is coming is that after the named range is set it is not seeing the actual values in the name manager in excel. For Example: (when opening the name manager in excel)

    The names are correct (they are set to WC and the value of the cell) but the values are coming in as - Active Items!$a:$BA$397 (I think this is where the problem is when I manually name ranges it shows teh values of the different cells...

    It is referencing the right cells so I am really at a lost! Any help is greatly appreciated!

    Thanks Again,.
    Mike
  6. #4
  7. Moderator
    ASP Super Genius (4500 - 4999 posts)

    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    Here is link to another site thread with similar issue. Seems to indicate that the equal sign (=) must be included in the string. Also apostrophe delimiters are used, although doubt those are necessary.
    http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2007-12/msg03505.html

    Excerpt:
    ActiveWorkbook.Names.Add Name:=IndName, _
    RefersToR1C1:= "='" & WSName & "'!R2C1:R" & MaxRng & "C120"
    Last edited by June7; July 21st, 2009 at 07:46 PM.

IMN logo majestic logo threadwatch logo seochat tools logo