Dynamic named ranges

G

Guest

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
 
G

Guest

I don't see where you need Named ranges here. If you want to refer to a 3 x
16 range

activecell.Resize(3,16).copy

something like that.
 
G

Guest

You are right, I don't. Thank you so much for that. I know I am
complicating the coding more than it has to be but this is my first attempt
at Excel VBA. Now I'm hitting an error in my sub that says
run-time error '1004'
Method 'Range' of object _Global failed

when the code--Set r = Range("M_Index") is reached. I've hit the Help button
and nothing comes up from the VB help file.


"I'm trying to determine if the account number in the first column already
exists on the worksheet I am getting ready to paste the record to. I am
moving the first cell from the current record "M_Index" to compare to the
first column in the activeworksheet.

Public Sub BAR_FU(M_Index)
Sheets("BAR").Select


ActiveCell.Offset(5, 0).Activate
Set r = Range("M_Index")
For n = 1 To r.Rows.Count
If r.Cells(n, 1) = r.Cells(n + 1, 1) Then
MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address
End If
Next n

If ActiveCell("a6") And IsEmpty(ActiveCell) Then
ActiveSheet.Paste
Else
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0)) And _
IsEmpty(ActiveCell.Offset(1, 0))
ActiveSheet.Paste
Loop
End If
 
G

Guest

do you have a defined name M_Index?

Sub test3()
Dim r As Range
ActiveCell.Name = "M_Index_1"
Set r = Range("M_Index_1")
MsgBox r.Address(0, 0, xlA1, True) & _
vbNewLine & Range("M_Index_1") _
.Address(0, 0, xlA1, True)
End Sub


would indicate there is nothing wrong with you line of code if M_Index exits
(I used a different name so as not to mess with yours).
 
G

Guest

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
 
T

Tom Ogilvy

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

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.
 
G

Guest

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.
 
R

Randy Harmelink

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data samples 3
Combine 3 Macros 4
Macro Error in Command Button 4
Deactivate macros but reactivate again once another macro is run 2
spreadsheet overwrite problem! I dont know what is wrong in my cod 6
macro to copy rows that contain a cell with a certain number 2
=today() - 1 4
VB Code help 1

Top