Ensure Named Range is in ThisWorkbook
I'm working on a project where I will have numerous workbooks open with
lots of named ranges. I'm trying to figure out how to ensure that when I call a named range, ie: dim myRange as Range set myRange = Range("Named Range") that I am getting the range in the workbook that the macro is written in. Really, I want this: dim myRange as Range set myRange = Range(ThisWorkbook."Named Range") but it obviously doesn't work like that (just I hope an easy way of explaining what I'm looking for). Thanks in advance, John |
Ensure Named Range is in ThisWorkbook
John,
John: In the Thisworkbook module, tests for range in the active workbook: Sub rangetest() On Error Resume Next If Range("Test").Name Is Nothing Then MsgBox "Range does not exist" Else: MsgBox "Range does exist" End If End Sub Alan John Fuller wrote: I'm working on a project where I will have numerous workbooks open with lots of named ranges. I'm trying to figure out how to ensure that when I call a named range, ie: dim myRange as Range set myRange = Range("Named Range") that I am getting the range in the workbook that the macro is written in. Really, I want this: dim myRange as Range set myRange = Range(ThisWorkbook."Named Range") but it obviously doesn't work like that (just I hope an easy way of explaining what I'm looking for). Thanks in advance, John |
Ensure Named Range is in ThisWorkbook
John, use thiskind ofscript :
Sub test() Dim rMyRange As Range Set rMyRange = ThisWorkbook.Names("myRange").RefersToRange End Sub where "ThisWorkbook"is the default "CodeName" for the workbook in the visual basic project (but it can be modified to be clearer like wkbDataTransfered) "John Fuller" wrote: I'm working on a project where I will have numerous workbooks open with lots of named ranges. I'm trying to figure out how to ensure that when I call a named range, ie: dim myRange as Range set myRange = Range("Named Range") that I am getting the range in the workbook that the macro is written in. Really, I want this: dim myRange as Range set myRange = Range(ThisWorkbook."Named Range") but it obviously doesn't work like that (just I hope an easy way of explaining what I'm looking for). Thanks in advance, John |
Ensure Named Range is in ThisWorkbook
John,
MsgBox Range("rngTest").Parent.Parent.Name=ThisWorkbook.N ame NickHK "John Fuller" wrote in message ps.com... I'm working on a project where I will have numerous workbooks open with lots of named ranges. I'm trying to figure out how to ensure that when I call a named range, ie: dim myRange as Range set myRange = Range("Named Range") that I am getting the range in the workbook that the macro is written in. Really, I want this: dim myRange as Range set myRange = Range(ThisWorkbook."Named Range") but it obviously doesn't work like that (just I hope an easy way of explaining what I'm looking for). Thanks in advance, John |
Ensure Named Range is in ThisWorkbook
Thanks all. I'll give them a try.
NickHK wrote: John, MsgBox Range("rngTest").Parent.Parent.Name=ThisWorkbook.N ame NickHK "John Fuller" wrote in message ps.com... I'm working on a project where I will have numerous workbooks open with lots of named ranges. I'm trying to figure out how to ensure that when I call a named range, ie: dim myRange as Range set myRange = Range("Named Range") that I am getting the range in the workbook that the macro is written in. Really, I want this: dim myRange as Range set myRange = Range(ThisWorkbook."Named Range") but it obviously doesn't work like that (just I hope an easy way of explaining what I'm looking for). Thanks in advance, John |
All times are GMT +1. The time now is 05:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com