Naming ranges (XL97/WinNT4)

Topic

New Reply

Could someone help me with this problem, please? I’ve written the following code, but XL is not my strong point, loops are one of my weakest points and it’s neither of these that are causing the problem (so far)!

Could someone tell me how to phrase the line that names the range so that the currently selected range is given the name “day” plus the current value of the variable dayNo? The phraseology below (“referstolocal=dayeach”) is obviously incorrect, but I haven’t been able to come up with anything else!

Sub FindFeb01()
Dim dayNo As Integer, dayEach As Range
dayNo = 1
Set dayEach = Sheets("Feb").Range("a1:b1")
For dayNo = 1 To 31
    Cells.Find(What:=dayNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
    dayEach = ActiveCell.Offset(1, 0).Range("A1:C3").Select
    ActiveWorkbook.Names.Add Name:="day" + dayNo, referstolocal=dayEach
    dayNo = dayNo + 1
Next
End Sub

Any help would be gratefully appreciated (not to mention helping reduce my headache)!

Reply | Quote

Replies

It won’t work this way: to assign a parameter, you need := instead of =, but RefersToLocal is a string, not a range. Instead of

dayEach = ActiveCell.Offset(1, 0).Range(“A1:C3″).Select
ActiveWorkbook.Names.Add Name:=”day” + dayNo, referstolocal=dayEach

use the much simpler

ActiveCell.Offset(1, 0).Range(“A1:C3”).Name = “day” & dayNo

Reply | Quote

Thanks, Hans, you’re a gem!

Now I get another problem – it runs once perfectly and once I’d realised I didn’t need “dayno = dayno + 1” as it increments automatically anyway, that was fine – but on the second loop it crashes on the cells.find line, saying run-time error 91, the object variable or with block variable is not set!

What am I doing now?

Reply | Quote

Sorry Hans, one more quick question – once I (we?!) get it to stop crashing on anything higher than 1 (which it is – I’ve tried it with several different numbers), can you tell me how to get it to ignore numbers that are not there?

You may have gathered that the 1 to 31 refers to days of the month, but since the spreadsheet in question only uses working days it misses two of every seven, and they will be different ones each time!

Many thanks again!

Reply | Quote

Try this:

Sub FindFeb01()
Dim dayNo As Integer, dayEach As Range, oCell As Range
Set dayEach = Sheets(“Feb”).Range(“a1:b1”)
For dayNo = 1 To 31
Set oCell = Cells.Find(What:=dayNo, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not oCell Is Nothing Then
oCell.Offset(1, 0).Range(“A1:C3”).Name = “day” & dayNo
End If
Next
End Sub

It doesn’t select the result of Find (if nothing was found, selecting causes an error), but assigns it to a Range variable. If nothing was found, this will be Nothing.

Reply | Quote

Hans, many thanks, this works perfectly except for one small thing – I need to put in similar ranges on each of the other 11 sheets (Jan and Mar to Dec), but despite changing the references to ‘Feb’ to the relevant month in each case, all the ranges are set in the sheet ‘Feb’!

What am I missing?!

Reply | Quote

Beryl,

If you’re going to name all ranges “day” & dayno, the names should be local to the sheet they are on. There can only be one global name “day20″ in the workbook, so if you use global names, they would overwite each other, unless you included the month in the name.

Also, if you’re going to run the macro for different sheets, you should either have an explicit reference to the sheet, or make each sheet active in turn, and refer to ActiveSheet.

This macro will loop through all worksheets and assign local names:

Sub MakeALotOfNames()
Dim dayNo As Integer, oCell As Range, oSheet As Worksheet
For Each oSheet In ActiveWorkbook.Worksheets
For dayNo = 1 To 31
Set oCell = oSheet.Cells.Find(What:=dayNo, After:=oSheet.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not oCell Is Nothing Then
oSheet.Names.Add Name:=”day” & dayNo, _
RefersTo:=oCell.Offset(1, 0).Range(“A1:C3”).Address
End If
Next dayNo
Next oSheet
Set oCell = Nothing
Set oSheet = Nothing
End Sub

Reply | Quote

Hi Hans, yes, I had thought of that and so far I’ve come up with this:

Sub SetRanges()
Dim dayNo As Integer, oCell As Range, dayLen As String, wSheet As Worksheet
Dim wSheetNo As String

Application.ScreenUpdating = False

For Each wSheet In ActiveWorkbook.Worksheets
    wSheet.Select
        If wSheet.Name = "Jan" Then wSheetNo = "01"
        If wSheet.Name = "Feb" Then wSheetNo = "02"
        If wSheet.Name = "Mar" Then wSheetNo = "03"
        If wSheet.Name = "Apr" Then wSheetNo = "04"
        If wSheet.Name = "May" Then wSheetNo = "05"
        If wSheet.Name = "Jun" Then wSheetNo = "06"
        If wSheet.Name = "Jul" Then wSheetNo = "07"
        If wSheet.Name = "Aug" Then wSheetNo = "08"
        If wSheet.Name = "Sep" Then wSheetNo = "09"
        If wSheet.Name = "Oct" Then wSheetNo = "10"
        If wSheet.Name = "Nov" Then wSheetNo = "11"
        If wSheet.Name = "Dec" Then wSheetNo = "12"
    For dayNo = 1 To 31
        Set oCell = Cells.Find(What:=dayNo, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False)
        If Not oCell Is Nothing Then
            dayLen = dayNo
            If Len(dayLen) = 1 Then dayLen = "0" + dayLen
            oCell.Offset(1, 0).Range("A1:C3").Name = "day" & wSheetNo & dayLen
        End If
    Next
    Range("b2").Select
Next wSheet

Sheets("Jan").Select
'Application.ScreenUpdating = True

End Sub

How am I doing?!!

I’d like to find a neater way to name wSheetNo but although in the project explorer the sheets are named “Sheet01 (Jan)” I haven’t been able to find a way to refer to the ‘Sheet01’ bit!

Any suggestions?

Many thanks for your help, by the way!

Reply | Quote

Hi Beryl,

Here is a somewhat shorter version. It uses the Format function to get the month number and day number with leading zeroes. It doesn’t select anything, so I prefixed Cells with the sheet being worked on: wSheet.Cells(…), otherwise it would assign all names in the active sheet.

Sub SetRanges()
Dim dayNo As Integer, oCell As Range, wSheet As Worksheet
Dim wSheetNo As String

Application.ScreenUpdating = False

For Each wSheet In ActiveWorkbook.Worksheets
‘ Convert sheet name to month number
wSheetNo = Format(DateValue(“1 ” & wSheet.Name & ” 2003″), “mm”)
For dayNo = 1 To 31
‘ Find day number
Set oCell = wSheet.Cells.Find(What:=dayNo, After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not oCell Is Nothing Then
‘ Set name
oCell.Offset(1, 0).Range(“A1:C3”).Name = _
“day” & wSheetNo & Format(dayNo, “00”)
End If
Next dayNo
Next wSheet

Set oCell = Nothing
Set wSheet = Nothing
Application.ScreenUpdating = True
End Sub

Reply | Quote

Hans, you’re brilliant! In fact, you don’t even know how clever you are, because one of the next things I’ve got to work out how to do is to perform calculations on these ranges, based on dates – which you’ve just shown me the basics of how to do!

Basically, this spreadsheet is being used as a calendar, with various cells within each of the day ranges having figures in, and I am going to perform calculations such as “how much is put to credit notes over 60 days from today” for a summary sheet. This means first defining the range that represents today, and then telling it how to work out which ranges should be included for 60 days (and next day, and seven days, and 30 days and …) well, you get the picture!

There is one thing you could help me with, before I set to again – how do you use the background colour as a criterion for including it or not? Because the way the spreadsheet is set up is to have different colour cells to represent credit notes, rollovers, etc so I need to tell it (once it knows which ranges to include) that I want only the values of the cells with a certain background colour for this particular calculation …

Many thanks again, Hans, you’re definitely the best!

Reply | Quote

Beryl,

I hope that the replies to post 29778 will give you some ideas how to do calculations based on the background colour of cells.

Reply | Quote

Thanks, Hans, that’s perfect! Or at least, I’m pretty sure it will be when I get that far …!

Do you mind if I ask a couple more incey-wincey little questions …? Pretty please?!

First, it just occurred to me that when the forward calculations go over the end of the year, the remainder of the ranges involved will be in a different workbook – in fact, the summary sheet will probably be in a different workbook anyway! – so how do you refer to another workbook entirely?

and second, is there any way of naming a group of ranges that are not contiguous? As you’ll have noticed above, I have a range representing each day, and for the 60-day calculation (for example) I’ll need to say something like “with the group of ranges that fall within the range representing 60 days from now, add the values of all cells whose background colour is blue …”

I do hope that second one is possible or I’m going to have to go the very complicated route of having it calculate a value for each day-range with blue cells one after the other, recalculating for each range whether it falls within the required range or not each time!

Many thanks again, Hans!

Reply | Quote

Hi Beryl,

Names can refer to ranges that consist of multiple areas, and to ranges in other workbooks.

Examples of the RefersTo property of a name:
=Aug!$D$8:$E$17,Aug!$G$8:$I$16
=[Test.xls]Aug!$B$6:$H$20,[Test.xls]Aug!$J$6:$O$20

The first refers to a range consisting of two areas in the ‘Aug’ worksheet in the current workbook.
The second refers to a range consisting of two areas in the ‘Aug’ worksheet in the workbook Test.xls.

Note: in order to use names referring to ranges in another workbook, that workbook must be open. Referring to a range in a closed workbook causes an error.

Reply | Quote

So I would be looking at something along the lines of

= Aug!day0102, Aug!day0103 … etc?

Reply | Quote

Without knowing all details, your current setup seems a complicated one to do calculations with!

To use Excel’s power, a single table containing al dates below each other -with next to them all properties needed- is by far the easiest method.

I would consider changing the design so that you have this single table holding your data and use a macro to update both your table and the corresponding cell colour in the nicely formatted sheets.

BTW: Are you familiar with my name manager add-in (available at the Excel MVP page below)?

Reply | Quote

Hi Jan – no, I wasn’t familiar with the Name Manager before you mentioned it – it looks like an interesting bit of code, but I’m not supposed to download stuff here at work so I think I’ll leave it for the moment.

However, your table for referencing data idea sounds interesting – it’s not something I’ve tried before, would you mind going into more detail?

If you want to see what I’m talking about, the attachment to my post further up the thread is a zipped-up copy of my spreadsheet – it is created by a template wherein a user specifies the year they’re creating and the dates within each month are then entered. Once they have this spreadsheet, they select a cell, click on the button on the right for the required colour scheme (key at the bottom of the sheet) and fill in the number.

The reason for the layout is historical (it used to be pieces of paper, coloured in by hand!) and the users using it are the type who have only just discovered the calculator, let alone a computer, so I see no chance of changing the layout, if that was what you were suggesting?

Reply | Quote

What I meant to say is that you setup a sheet caled (e.g.) AllData, which could contain a table like this:

Date	Floating Rate Note Rollovers	Secured	Unsecured	Eligible Bills	CDs	Floating Rate Notes	Bank Holidays	Target Days
01-01-2003	1	2	3	4	5	6	7	8
02-01-2003	8	7	6	5	4	3	2	1

I just made up the numbers in the first two rows of data.

Your macro that colours the cells now needs to do two things:

1: update the cell colour
2: Update the table (and maybe ask for the number to put in both the cell AND the table)

You will need to instruct your users to update numbers in the table using your buttons only.

Reply | Quote

Hans, is it possible to refer to a cell within a range, as when we set up the offsets and referred to a1:c1 within range day0101?

The reason I ask is that I need to refer to one of the cells within each day range, the big one on the bottom (C1 within the range) and the only way this is different from other cells is that it is always in position C1 within the day range!

Using your suggestion earlier, the main reference would be “=[cw target 2003]!day0101” (for January 1st), but how do I then refer to the cell at position C1 in that range? Or is it not possible …?

Reply | Quote

msgbox range(activeworkbook.Names(“day0101”)).Cells(3,1).address

Reply | Quote

Wow! I am glad you told me that because my wildest attempts at doing it myself didn’t come anywhere close – I don’t think I would ever have worked it out!

Reply | Quote

Jan, I’m sorry, but I’m still getting stuck, even though you’ve taken me 99% of the way there! My current effort stands at:

msgbox range(workbooks(“n:bimcw targetcw_target_2003.xls”).Names(“day0103”)).Cells(3,1).value

because I need to specify the workbook (it’s not going to be the active one, although it will be open), but it’s not working and I can’t work out why! This particular version gets a ‘subscript out of range’ error, but I don’t understand the ‘help’ text, so I can’t work out what’s wrong!

Any further help would be much appreciated!

Reply | Quote

That is an easy one :

msgbox range(workbooks(“cw_target_2003.xls”).Names(“day0103”)).Cells(3,1).value

Reply | Quote

Yes, that’s what I thought, too – so why did I get:

“Run-time error 1004: application-defined or object-defined error”

when I ran it in the immediate window? And yes, the spreadsheet was open – that was the first thing I checked!

My head hurts.

Reply | Quote

Because it only works when the workbook is the active workbook.

I’ll have to dig into this to get it working. Maybe tomorrow…

Reply | Quote

Thanks for your help, Jan

Reply | Quote

Found it:

MsgBox Workbooks(“cw target 2003a.xls”).Names(“day0101”).RefersToRange.Cells(3, 1).Address

Reply | Quote

Nope – I still get the ‘application-defined …’ error.

Having said that, I’ve been looking into your idea of a table, which is looking better all the time, but which means the reference we’re looking for will be in an ordinary XL cell, rather than in code, and they’re not the same …?!

Reply | Quote

I tested this a bit more and discovered that I can replicate the error when the name one tries to access does not exist.

Reply | Quote

No, I take that back – I’ve just realised that since the workbook name has to be created programmatically (so the year changes as we go along) the ranges are going to have to be referred to in VBA, after all!

? I am!

Reply | Quote

Jan, I’ve cracked it! Believe it or not, it was objecting to the name being used in that circumstance! As soon as I put the worksheet name into a variable and used that instead of the full name, it worked fine.

Of course, I’d love to know *why* it objected like that …

But I’ll just accept it does!!!!!

Many, many thanks – I wouldn’t got it without yours and Hans help!

Reply | Quote

Could you post the relevant lines that didn’t work and the ones that do work? I’m not sure I understood how you resolved the issue.

Reply | Quote

Jan, this is the version that worked (rangeName and docName were previously defined using dates to get “day0103” and “cw target 2003.xls” respectively):

Set wBook = Workbooks(docName)
Workbooks(“liquid.xls”).Worksheets(“dates”).Range(“c3”).Value = wBook.Names(rangeName).RefersToRange.Cells(3, 1).Value

As opposed to any version that still contained the actual workbook name as “workbooks(“cw target 2003.xls”)”, which would not work! I even replaced the spaces in the name with underscores to see if that was what was causing it, but it didn’t make any difference.

As I said, I’ve no idea why that works, but it does!

Reply | Quote

It just occurred to me that life might be made a lot easier if I posted the spreadsheet for you to look at …!

The code for SetRanges will go in the template this is based on, so the ranges will only be set once, when the workbook is created for the year in question.

Reply | Quote

Hans, sorry, I’ve just noticed a problem – the SetRanges sub works perfectly, but it only sets one range in each month, for the first day it finds – this can be anything from the 1st to the 3rd (I’m only using working days) so I’m getting day0101, day0203, day0303, day0401, etc

Looking at the code, it looks like it ought to be looping OK – I can’t see why it isn’t!

Reply | Quote

I think I might have spotted why it’s doing that – in each sheet, the first number is just typed in, the rest are calculated from that one, so those cells contain formulae rather than straight figures – do you think this is it? and if so, how do I stop it?!

Reply | Quote

Hi Beryl,

The Find method has an argument Lookin that determines whether Excel looks in the formulas, values or notes of the cells. In the current version, you have Lookin:=xlFormulas; if you replace this by Lookin:=xlValues, you should be OK.

Reply | Quote

Brilliant!

I am learning so much through this project …

Reply | Quote

hi Beryl,

if i’m not mistaken and looking at your setup and the arguments of your find, it seems the procedure below will do what you want, without a find:

Sub FindFeb01_pj()
Dim oCell As Range
For Each oCell In _
Range _
( _
Sheets(“Feb”).Range(“a1”), _
Sheets(“feb”).Range(“a65536”).End(xlUp) _
)
If _
IsNumeric(oCell) And _
oCell.Value > 0 And oCell.Value < 32 _
Then
oCell.Offset(1, 0).Range("A1:C3").Name = "day" & oCell
End If
Next
End Sub

greetings,

Reply | Quote

Thanks, Pieter – apart from being set (as far as I can see) to only check column A, that looks like it should work, according to my limited knowledge of XL VBA – but when I ran it, it didn’t!

I corrected the range to cover just the section I wanted (A4:Q20), but if I just ran it, it crashed on a ‘type mismatch’ on the ‘If IsNumeric …’ line; if I put a break on the ‘For each …’ line (I wanted to wach what it was doing, using F8) it runs fine, but doesn’t do anything! At least, when I stopped it and checked for any new ranges there were none there.

I is confuggulated!

Reply | Quote