+ Reply to Thread
Results 1 to 12 of 12

Qualify reference for named range in ThisWorkbook

  1. #1
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Qualify reference for named range in ThisWorkbook

    Hi all,

    I'm still getting slightly confused how to correctly qualify references of ranges if they live in the workbook the codes resides in. I was initially assuming they automatically refer to ThisWorkbook, but it seems they actually refer to the ActiveWorkbook instead, if not qualified explicitly. Below code gets the wrong column as the named range "Attribute_FlowDelivery" exists in BOTH workbooks, hence it takes the ActiveWorkbook rather than ThisWorkbook.

    What are the best practises to qualify all cells and ranges in below example? Do I have to set the worksheet and do sht.Range("Attribute_FlowDelivery").Column in my .FormulaR1C1 or is there a better way?

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Qualify reference for named range in ThisWorkbook

    to qualify the code you need to add a dot
    so .FormulaR1C1 = "=IF(RC" & Range("Attribute_FlowDelivery").Column & "="""","""",UPPER(TEXT(RC" & Range("Attribute_FlowDelivery").Column & ",""mmm"")))"
    will look
    .FormulaR1C1 = "=IF(RC" & .Range("Attribute_FlowDelivery").Column & "="""","""",UPPER(TEXT(RC" & .Range("Attribute_FlowDelivery").Column & ",""mmm"")))"

    cause of your with statement the dot before will be the same as ThisWorkbook.Sheets(SH_Recap.Index).Range("Attribute_FlowDelivery").Column
    the with statement has kind of put it into short hand


  3. #3
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Qualify reference for named range in ThisWorkbook

    If you just add a dot, it will be relative to the range the with statement refers to. E.g. if With .Cells(StrtRw, .Range("Attribute_DeliveryMonth").Column) refers to column 8 and Range("Attribute_FlowDelivery").Column refers to column 6 then .FormulaR1C1 will become RC14 rather than RC6...

  4. #4
    Valued Forum Contributor
    Join Date
    05-14-2009
    Location
    gold coast
    MS-Off Ver
    Excel 2007
    Posts
    843

    Re: Qualify reference for named range in ThisWorkbook

    honestly you just lost me.

    though you where just having issues with non quaified code refering to a active workbook.

    sorry though someone else might chime in. or ill take a look if you have a book

  5. #5
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Qualify reference for named range in ThisWorkbook

    If you know the sheet, specify it.
    Rory

  6. #6
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Qualify reference for named range in ThisWorkbook

    Quote Originally Posted by rorya View Post
    If you know the sheet, specify it.
    So there’s no other way? I do know the sheet, but there are quite a few ranges I would have to specify, so was wondering if there’s a more convenient method?

  7. #7
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Qualify reference for named range in ThisWorkbook

    You can use thisworkbook.names("...").referstorange

  8. #8
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Qualify reference for named range in ThisWorkbook

    But I would still have to do that for every single named range? Was hoping there's a way I could use WITH statements as well, so I won't have to repeat things too much...

  9. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Qualify reference for named range in ThisWorkbook

    You can use With statements with any Object so I don't really follow what you mean by that.

  10. #10
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481

    Re: Qualify reference for named range in ThisWorkbook

    But I cannot do:

    Please Login or Register  to view this content.
    I would need to specify the sheet or referstorange for every single named range as below:

    Please Login or Register  to view this content.
    correct? Or is there a more convenient way?

  11. #11
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,885

    Re: Qualify reference for named range in ThisWorkbook

    Yes, of course. How would the code possibly know that one dot should refer to one object and one to another? You could just assign the column number to a variable prior to that With block.

  12. #12
    Forum Contributor
    Join Date
    07-13-2017
    Location
    Hong Kong
    MS-Off Ver
    MS Office 365
    Posts
    481
    Ok, thanks. Just wanted to double check with experts like yourself, if I’m missing something! I have quite a few ranges I need to qualify and was worried about human error... (all different ranges, hence assigning the column to a variable won’t help either)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Fully Qualify Range Utilizing Union
    By ulrick65 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-08-2017, 06:37 PM
  2. Replies: 0
    Last Post: 04-09-2015, 10:03 AM
  3. [SOLVED] ThisWorkbook code not hiding named sheets
    By neelie777 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-03-2014, 06:11 PM
  4. [SOLVED] How to properly qualify a range
    By ungers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2013, 11:14 AM
  5. How to reference a Named Range when the range contains text, not numbers
    By kcgatorz in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-09-2013, 02:57 PM
  6. Replies: 1
    Last Post: 03-21-2006, 06:40 PM
  7. Query on Date range does not qualify the Year
    By Malcolm Makin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-23-2005, 02:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1