Defining ranges in VB code

[Rachael Moody]

Hi,

I'm sorry if this seems so trivial to you but I'm trying
to teach myself Visual Basic using the help facility and a
Microsoft Office2000 VBA Fundamentals book. The first
project I have set myself is to develop a financial
planner, the workbook contains 1 template worksheet, 1
worksheet containing reference data and one sheet per
month. My aim is to copy the template worksheet, rename it
for the current calendar month and define ranges on the
new sheet so values can be brought forward to reflect the
balance of my back account.

I am trying to define named RANGES (using VB code) to the
worksheet by passing 2 string variables to 2 of the
parameters to the ACTIVEWORKBOOK.NAMES.ADD command
The parameters being Name:
and RefersToRange:

At the moment the code gives the appearance of working
though the defined RANGES do not appear in the NAME BOX as
I would expect and therefore I can not access the defined
ranges. The defined RANGEs though do appear if I go to the
Insert drop down menu, select the Name option then Define
option!

The range DefineRanges contains 4 columns of the ranges to
be defined on the new worksheet
Column 1 - name of the range to be defined
e.g. "jandirectdebit" on the monthly worksheet
Column 2 - start of the RefersToR1C1 range
Column 3 - end of the RefersToR1C1 range
Column 4 - RefersTo information

The code reads:
Set r = range("DirectDebitName") 'details of ranges
to be defined
For i = 1 to r.rows.count
strRangeName = r.cells(i, 1)
strStartOfRange = r.cells(i, 2)
strEndOfRange = r.cells(i, 3)
strRefersTo = r.cells(i, 4)

ActiveWorkBook.Names.Add Name:strRangeName,
RefersToR1C1:=strStartOfRange & ":" & sreEndOfRange
Next i

I have also tried
ActiveWorkBook.Names.Add Name:strRangeName,
RefersTo:=strRefersTo

No matter what I try I can not define named ranges

HELP!!!

 

[Jan Karel Pieterse]

Hi Rachael,

I have also tried
ActiveWorkBook.Names.Add Name:strRangeName,
RefersTo:=strRefersTo

No matter what I try I can not define named ranges

Make that:

ActiveWorkBook.Names.Add Name:strRangeName,
RefersToR1C1:=Activeworkbook.Activesheet.Range(strStartOfRange & ":" &
sreEndOfRange)

Note that by defining these names the "old" names (to the other sheets)
will be overwritten.

NB: Download my name manager from my website!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

 

[Bob Phillips]

Rachael,

There are various flavours of names, local (to a worksheet) and global (to
all worksheets in a workbook). In addition you have range names and names
that hold values. You are creating the latter, with the values in the celles
that you reference, not the cells themseleves.

Try this

Set r = Range("DirectDebitName")
For i = 1 To r.Rows.Count
strRangeName = r.Cells(i, 1)
strStartOfRange = r.Cells(i, 2).Address
strEndOfRange = r.Cells(i, 3).Address
strRefersTo = r.Cells(i, 4).Address


ActiveWorkbook.Names.Add Name:=strRangeName, RefersTo:="=" & strStartOfRange
& ":" & strEndOfRange
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[BrianB]

Code seems OK except that we have to add the "=" to RefersTo, therefor
(partial) :-

RefersToR1C1:="=" & Application.Activesheet.Range(strStartOfRange & ":
& sreEndOfRange)

Don't need ActiveWorkbook because ActiveSheet is at application leve
too. Cannot check further without seeing your data. You could put
line in to check :-

Msgbox(ActiveSheet.Range(strRangeName).Address

 

[BrianB]

Sorry, I still got it wrong. I am using Excel 97 and the RefersT
argument has to be a string. So something that comes out like this i
correct :-

RefersToR1C1:="=" & ActiveSheet.Name & "!" & "R1C8:R20C8"

So in the Insert/Name .. box it shows as something like :-
=Sheet1!$H$1:$H$2

 

[Jan Karel Pieterse]

Hi BrianB,

Code seems OK except that we have to add the "=" to RefersTo, therefore
(partial) :-

You are right, I made a mistake.

This should do it:

Activesheet.Range(strStartOfRange & ":" &
sreEndOfRange).Name=strRangeName

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com