Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: Defiine a Named Range in Excel and capture the Values

Results 1 to 4 of 4
Share This Thread →
  1. #1
    sniper2131 is offline Registered User
    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
    June7's Avatar
    June7 is offline Moderator
    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 ':'

  3. #3
    sniper2131 is offline Registered User
    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

  4. #4
    June7's Avatar
    June7 is offline Moderator
    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.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

ASP Free Advertisers and Affiliates