Dynamic named ranges

> ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> Set M_Index = Range("A:A")

There is no relationship between M_Index the named range and M_Index the
variable which you have set. Once you set the named range, you don't seem
to use it. so it is pointless.

Exits should have been exists.

My code worked fine for me.

It was meant to illustrate how to refer to a named range, but you seemed to
several concepts confused.

Maybe you could describe a simple task you want to do and someone can
provide some sample code. Then begin to expand you knowledge. From what I
have seen, you can forget about named ranges - but you can't seem to let it
go.

--
Regards,
Tom Ogilvy


"SFoley" <SFoley@discussions.microsoft.com> wrote in message
news:3739FFAC-F666-426B-9940-F26D08AE19E4@microsoft.com...
> Well, this is what is making me uncomfortable about Excel VBA. I'm
guessing
> I've named M_Index.
> Here's my code:
>
> Acct_Canc_Project.Sheet1.Activate
> ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> Set M_Index = Range("A:A")
> M_Index.Activate
> ActiveCell.Offset(rowOffset:=5, columnOffset:=0).Activate
> Set M_Index = ActiveCell
>
> Is that all I have to do to name a range is Names.AddName and Set commands
> or do I need to do something else?
>
> Also, you mention something about as long as my M_Index exits. What does
> that mean?
>
> Using your code, but replacing M_Index where there was M_Index_1 gave me
an
> error message of
> Run time error
> Method 'Range' of object'_Global' failed
>
>
> "SFoley" wrote:
>
> > I don't understand how the naming ranges works in Excel VBA. Once I
define
> > them in the spread sheet part, what are the requirements to be able to
use
> > them in the VBA code? I've looked in various places and there is
> > information, but it never seems to start from scratch.
> >
> > I am trying to move a record from the active worksheet to another
worksheet
> > in the same workbook. Basically each record is three rows long. The
record
> > may or may not, based on an indicator, need to be copied. I've have
managed
> > to get that far, how I'm not sure. What has really stopped me is trying
to
> > identify the range, which is dynamic, based on the active cell. From
the
> > active cell I need to include 2 rows down and 16 columns to the right in
a
> > range to copy to another worksheet in the same workbook. I really don't
> > understand how the naming a range process works in VBA. Is there anyone
who
> > can assist with this?
> >
> > My scary code is as follows:
> > Dim booBAR_FU_Y As Boolean
> > Dim booLAB_FU_Y As Boolean
> > Dim booNUR_FU_Y As Boolean
> > Dim booOE_FU_Y As Boolean
> > Dim booPHA_FU_Y As Boolean
> > Dim booRAD_FU_Y As Boolean
> > Dim booInd As Boolean
> > Dim M_Rec_Start_Cell As Variant
> > Dim M_Rec_End_Cell As Variant
> >
> > Dim M_Index As Range
> > Dim Rec_Range As Range
> > Dim M_Rec_No_counter As Long
> > Dim BLN_Indicator As String
> > Dim Second_Indicator As Range
> > Dim FI_Counter As Integer
> > Dim r As Long
> > Dim z As Variant
> > 'Dim BLN_Follow_Up As Range
> > ' Dim OPR_Follow_Up As Range
> >
> > Acct_Canc_Project.Sheet1.Activate
> > ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> > Set M_Index = Range("A:A")
> > M_Index.Activate
> > ActiveCell.Offset(rowOffset:=5, columnOffset:=0).Activate
> >
> > 'Set M_Index = Range("A:A")
> >
> > M_Rec_No_counter = 0
> >
> > 'Set Do loop to stop when three consecutive empty cells are reached.
> > Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
And _
> > IsEmpty(ActiveCell.Offset(1, 0))
> >
> > For Each m In ActiveCell
> > If m.Value Like "M*" Then
> > M_Rec_Start_Cell = ActiveCell.Address
> > ActiveCell.Offset(rowOffset:=2, columnOffset:=16).Select
> > M_Rec_End_Cell = ActiveWindow.RangeSelection.Address
> > Call Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
> > MsgBox ActiveWindow.RangeSelection.Address
> >
> > Set Rec_Range = Range("Rangeaddress")
> > Rec_Range.Copy
> >
> > 'Trying to determine if this record needs to be copied to one of
the
> > other areas
> > ActiveCell.Offset(0, 11).Activate
> >
> > 'This record will have three rows associated with it
> > For i = 1 To 3
> >
> > If i = 2 Then
> > ActiveCell.Offset(1, 0).Activate
> > End If
> >
> > If i = 3 Then
> > ActiveCell.Offset(1, 0).Activate
> > End If
> >
> > BLN_Follow_Up = ActiveCell
> >
> > 'Checking if any of the L columns in the record are marked "y"
> > If BLN_Follow_Up = "y" _
> > Then
> > 'i.value = 1 then it must be BAR that needs to follow up,
2=Lab
> > 3=Nur
> > If i = 1 Then
> > booBAR_FU_Y = True
> > End If
> > If i = 2 Then
> > booLAB_FU_Y = True
> > End If
> > If i = 3 Then
> > booNUR_FU_Y = True
> > Else
> > 'Error routine
> > End If
> > End If
> > Next
> >
> > Sub Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
> > '
> > ' Define_Rec_Range Macro
> > ' Macro recorded 4/10/2006 by Shelagh Foley
> > '
> >
> > '
> > ActiveWindow.ScrollColumn = 9
> > ActiveWindow.ScrollColumn = 8
> > ActiveWindow.ScrollColumn = 7
> > ActiveWindow.ScrollColumn = 6
> > ActiveWindow.ScrollColumn = 5
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 1
> > ActiveWindow.ScrollColumn = 10
> > ActiveWindow.ScrollColumn = 9
> > ActiveWindow.ScrollColumn = 8
> > ActiveWindow.ScrollColumn = 7
> > ActiveWindow.ScrollColumn = 6
> > ActiveWindow.ScrollColumn = 5
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 1
> > ActiveWindow.ScrollColumn = 10
> > ActiveWindow.ScrollColumn = 9
> > ActiveWindow.ScrollColumn = 8
> > ActiveWindow.ScrollColumn = 7
> > ActiveWindow.ScrollColumn = 6
> > ActiveWindow.ScrollColumn = 5
> > ActiveWindow.ScrollColumn = 4
> > ActiveWindow.ScrollColumn = 3
> > ActiveWindow.ScrollColumn = 2
> > ActiveWindow.ScrollColumn = 1
> > Range("A6:P17").Select
> > ActiveWorkbook.Names.Add Name:="Rec_Range", RefersToR1C1:= _
> > "=Main!R6C1:R17C16"
> >
> > Dim RangeAddress As String
> > RangeAddress = Names("Rec_Range").RefersTo
> > End Sub
> >
> >
> >

Well, I'm looking for someone to tell me something I don't know. That is
what I wrote in the very beginning of this thread:

"I don't understand how the naming ranges works in Excel VBA. Once I
define them in the spread sheet part, what are the requirements to be able to
use them in the VBA code? I've looked in various places and there is
information, but it never seems to start from scratch. I am trying to move a
record from the active worksheet to another worksheet in the same workbook.
Basically each record is three rows long. The record may or may not, based
on an indicator, need to be copied. "

It just seems the code would be easier with named ranges.

"Tom Ogilvy" wrote:

> > ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> > Set M_Index = Range("A:A")
>
> There is no relationship between M_Index the named range and M_Index the
> variable which you have set. Once you set the named range, you don't seem
> to use it. so it is pointless.
>
> Exits should have been exists.
>
> My code worked fine for me.
>
> It was meant to illustrate how to refer to a named range, but you seemed to
> several concepts confused.
>
> Maybe you could describe a simple task you want to do and someone can
> provide some sample code. Then begin to expand you knowledge. From what I
> have seen, you can forget about named ranges - but you can't seem to let it
> go.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "SFoley" <SFoley@discussions.microsoft.com> wrote in message
> news:3739FFAC-F666-426B-9940-F26D08AE19E4@microsoft.com...
> > Well, this is what is making me uncomfortable about Excel VBA. I'm
> guessing
> > I've named M_Index.
> > Here's my code:
> >
> > Acct_Canc_Project.Sheet1.Activate
> > ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> > Set M_Index = Range("A:A")
> > M_Index.Activate
> > ActiveCell.Offset(rowOffset:=5, columnOffset:=0).Activate
> > Set M_Index = ActiveCell
> >
> > Is that all I have to do to name a range is Names.AddName and Set commands
> > or do I need to do something else?
> >
> > Also, you mention something about as long as my M_Index exits. What does
> > that mean?
> >
> > Using your code, but replacing M_Index where there was M_Index_1 gave me
> an
> > error message of
> > Run time error
> > Method 'Range' of object'_Global' failed
> >
> >
> > "SFoley" wrote:
> >
> > > I don't understand how the naming ranges works in Excel VBA. Once I
> define
> > > them in the spread sheet part, what are the requirements to be able to
> use
> > > them in the VBA code? I've looked in various places and there is
> > > information, but it never seems to start from scratch.
> > >
> > > I am trying to move a record from the active worksheet to another
> worksheet
> > > in the same workbook. Basically each record is three rows long. The
> record
> > > may or may not, based on an indicator, need to be copied. I've have
> managed
> > > to get that far, how I'm not sure. What has really stopped me is trying
> to
> > > identify the range, which is dynamic, based on the active cell. From
> the
> > > active cell I need to include 2 rows down and 16 columns to the right in
> a
> > > range to copy to another worksheet in the same workbook. I really don't
> > > understand how the naming a range process works in VBA. Is there anyone
> who
> > > can assist with this?
> > >
> > > My scary code is as follows:
> > > Dim booBAR_FU_Y As Boolean
> > > Dim booLAB_FU_Y As Boolean
> > > Dim booNUR_FU_Y As Boolean
> > > Dim booOE_FU_Y As Boolean
> > > Dim booPHA_FU_Y As Boolean
> > > Dim booRAD_FU_Y As Boolean
> > > Dim booInd As Boolean
> > > Dim M_Rec_Start_Cell As Variant
> > > Dim M_Rec_End_Cell As Variant
> > >
> > > Dim M_Index As Range
> > > Dim Rec_Range As Range
> > > Dim M_Rec_No_counter As Long
> > > Dim BLN_Indicator As String
> > > Dim Second_Indicator As Range
> > > Dim FI_Counter As Integer
> > > Dim r As Long
> > > Dim z As Variant
> > > 'Dim BLN_Follow_Up As Range
> > > ' Dim OPR_Follow_Up As Range
> > >
> > > Acct_Canc_Project.Sheet1.Activate
> > > ActiveWorkbook.Names.Add Name:="M_Index", RefersToR1C1:="=Sheet1!C1"
> > > Set M_Index = Range("A:A")
> > > M_Index.Activate
> > > ActiveCell.Offset(rowOffset:=5, columnOffset:=0).Activate
> > >
> > > 'Set M_Index = Range("A:A")
> > >
> > > M_Rec_No_counter = 0
> > >
> > > 'Set Do loop to stop when three consecutive empty cells are reached.
> > > Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
> And _
> > > IsEmpty(ActiveCell.Offset(1, 0))
> > >
> > > For Each m In ActiveCell
> > > If m.Value Like "M*" Then
> > > M_Rec_Start_Cell = ActiveCell.Address
> > > ActiveCell.Offset(rowOffset:=2, columnOffset:=16).Select
> > > M_Rec_End_Cell = ActiveWindow.RangeSelection.Address
> > > Call Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
> > > MsgBox ActiveWindow.RangeSelection.Address
> > >
> > > Set Rec_Range = Range("Rangeaddress")
> > > Rec_Range.Copy
> > >
> > > 'Trying to determine if this record needs to be copied to one of
> the
> > > other areas
> > > ActiveCell.Offset(0, 11).Activate
> > >
> > > 'This record will have three rows associated with it
> > > For i = 1 To 3
> > >
> > > If i = 2 Then
> > > ActiveCell.Offset(1, 0).Activate
> > > End If
> > >
> > > If i = 3 Then
> > > ActiveCell.Offset(1, 0).Activate
> > > End If
> > >
> > > BLN_Follow_Up = ActiveCell
> > >
> > > 'Checking if any of the L columns in the record are marked "y"
> > > If BLN_Follow_Up = "y" _
> > > Then
> > > 'i.value = 1 then it must be BAR that needs to follow up,
> 2=Lab
> > > 3=Nur
> > > If i = 1 Then
> > > booBAR_FU_Y = True
> > > End If
> > > If i = 2 Then
> > > booLAB_FU_Y = True
> > > End If
> > > If i = 3 Then
> > > booNUR_FU_Y = True
> > > Else
> > > 'Error routine
> > > End If
> > > End If
> > > Next
> > >
> > > Sub Define_Rec_Range(M_Rec_Start_Cell, M_Rec_End_Cell)
> > > '
> > > ' Define_Rec_Range Macro
> > > ' Macro recorded 4/10/2006 by Shelagh Foley
> > > '
> > >
> > > '
> > > ActiveWindow.ScrollColumn = 9
> > > ActiveWindow.ScrollColumn = 8
> > > ActiveWindow.ScrollColumn = 7
> > > ActiveWindow.ScrollColumn = 6
> > > ActiveWindow.ScrollColumn = 5
> > > ActiveWindow.ScrollColumn = 4
> > > ActiveWindow.ScrollColumn = 3
> > > ActiveWindow.ScrollColumn = 2
> > > ActiveWindow.ScrollColumn = 1
> > > ActiveWindow.ScrollColumn = 10
> > > ActiveWindow.ScrollColumn = 9
> > > ActiveWindow.ScrollColumn = 8
> > > ActiveWindow.ScrollColumn = 7
> > > ActiveWindow.ScrollColumn = 6
> > > ActiveWindow.ScrollColumn = 5
> > > ActiveWindow.ScrollColumn = 4
> > > ActiveWindow.ScrollColumn = 3
> > > ActiveWindow.ScrollColumn = 2
> > > ActiveWindow.ScrollColumn = 1
> > > ActiveWindow.ScrollColumn = 10
> > > ActiveWindow.ScrollColumn = 9
> > > ActiveWindow.ScrollColumn = 8
> > > ActiveWindow.ScrollColumn = 7
> > > ActiveWindow.ScrollColumn = 6
> > > ActiveWindow.ScrollColumn = 5
> > > ActiveWindow.ScrollColumn = 4
> > > ActiveWindow.ScrollColumn = 3
> > > ActiveWindow.ScrollColumn = 2
> > > ActiveWindow.ScrollColumn = 1
> > > Range("A6:P17").Select
> > > ActiveWorkbook.Names.Add Name:="Rec_Range", RefersToR1C1:= _
> > > "=Main!R6C1:R17C16"
> > >
> > > Dim RangeAddress As String
> > > RangeAddress = Names("Rec_Range").RefersTo
> > > End Sub
> > >
> > >
> > >
>
>
>

>> Once I define them in the spread sheet part, what are the requirements to be able to
use them in the VBA code? <<

Quite easy. For example, I have a "Settings" worksheet where I have a
number of named ranges so I can set values in the worksheet for the
macro that will be run:

If (Range("URL_Comics1") <> 0) Then fMailThem ("Comics 1")
If (Range("URL_Comics2") <> 0) Then fMailThem ("Comics 2")
If (Range("URL_Comics3") <> 0) Then fMailThem ("Comics 3")
If (Range("URL_Daily") <> 0) Then fMailThem ("Daily")
If (Range("URL_DailyTV") <> 0) Then fMailThem ("Daily TV")
If (Range("URL_Portfolio") <> 0) Then fMailThem ("Portfolio")
If (Range("URL_StockScreen") <> 0) Then fMailThem ("Stock Screen")

I'm just using each cell as an indicator (0 or 1) of whether or not to
execute a particular function. Or, I have another section that looks
like this:

If wsG.Range("MSN_FYI") = 1 Then
Call Paste_Table("" & sData, pTicker, ...)
End If
If wsG.Range("MSN_Owners") = 1 Then
Call Paste_Table("" & sData, pTicker, ...)
End If
If wsG.Range("MSN_Analysts") = 1 Then
Call Paste_Table("" & sData, pTicker, ...)
End If
If wsG.Range("MSN_Highlights") = 1 Then
Call Paste_Table("" & sData, pTicker, ...)
End If