Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Naming ranges (XL97/WinNT4)

    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!
    <pre>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</pre>

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


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Naming ranges (XL97/WinNT4)

    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

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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? <img src=/S/sad.gif border=0 alt=sad width=15 height=15>
    Beryl M


  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Naming ranges (XL97/WinNT4)

    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.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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,

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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! <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Beryl M


  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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?!
    Beryl M


  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Naming ranges (XL97/WinNT4)

    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

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

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

    <pre>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
    </pre>

    How am I doing?!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

    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!
    Beryl M


  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Naming ranges (XL97/WinNT4)

    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

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    Hans, you're brilliant! <img src=/S/starstruck.gif border=0 alt=starstruck width=15 height=15> In fact, you don't even know how clever <img src=/S/clever.gif border=0 alt=clever width=15 height=15> 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! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Naming ranges (XL97/WinNT4)

    Beryl,

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

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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!
    Beryl M


  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Naming ranges (XL97/WinNT4)

    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!
    Beryl M


Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •